Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I solve this problem? Please help me | Excel Discussion (Misc queries) | |||
Pls help me to solve this problem... | Excel Worksheet Functions | |||
please solve the problem | Excel Discussion (Misc queries) | |||
Can someone solve a problem for me? | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |