lookup problem..can you solve!??!

[Ben Miller]

I have a table that resembles the following:

COLA COLB COLC COLD
1 1 2003 3
1 2 2003 6
1 3 2003 9
1 1 2004 8
1 2 2004 4
1 3 2004 99
1 4 2004 5
2 1 2003 4
2 2 2003 7
2 3 2003 7

Does anybody know how I can enter a WORKSHEET FORMULA that
will return the value in D for given values for A,B and
C.

As an example if A=1 , B=3 and C=2004 are used in the
formula, then the formula should return the answer 99
(which is the value in D).

 

[Bill]

Easiest way I've found (not necessarily the right way) would be to create unique values in another column (ie: E = a + b + c)

A B C D E
1 1 2003 3 112003
1 2 2003 6 122003
1 3 2003 9 132003..
1 1 2004 8
1 2 2004 4
1 3 2004 99
1 4 2004 5
2 1 2003 4
2 2 2003 7
2 3 2003 7

Then the formula VLOOKUP(132004,"rangename",4,False) would return 99. The only caveat would be/is non-unique values.

 

[Tom Ogilvy]

=Sumproduct((A1:A10=1)*(B1:B10=3)*(C1:C10=2004)*D110)

if the combination will resolve to a single row.

 

[Ben Miller]

Hi thanks for your reply.

Just wondered if it was possible to do the calculateion
in one step!?!?

We considered the concatenation idea , its not practical
in the actual setup...

-----Original Message-----
Easiest way I've found (not necessarily the right way)

would be to create unique values in another column (ie: E
= a + b + c)

A B C D E
1 1 2003 3 112003
1 2 2003 6 122003
1 3 2003 9 132003..
1 1 2004 8
1 2 2004 4
1 3 2004 99
1 4 2004 5
2 1 2003 4
2 2 2003 7
2 3 2003 7

Then the formula VLOOKUP(132004,"rangename",4,False)

would return 99. The only caveat would be/is non-unique
values.

 

[ijb]

Call your data range MyDB, if you set up another section of the sheet like
this (call it MyCriteria):
COLA COLB COLC
1 3 2004
NOTE: COLA etc must be text in the first row of both MyCriteria and MyDB

Then (Excel XP) type the following formula "=DGET(MyDB,"COLD",MyCriteria)

I "think" older versions of Excel had a similar function called DLOOKUP

--
If I've mis-understood the question please tell me.

HTH

ijb

Replies to group please

Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help