lookup problem..can you solve!??!
 

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).

lookup problem..can you solve!??!
 

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.

"Ben Miller" wrote:

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).

lookup problem..can you solve!??!
 

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

if the combination will resolve to a single row.

--
Regards,
Tom Ogilvy

"Ben Miller" wrote in message
...
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).

lookup problem..can you solve!??!
 

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.

"Ben Miller" wrote:

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).



.

lookup problem..can you solve!??!
 

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


"Ben Miller" wrote in message
...
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).