Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mutliple criteria vlookups
I need a way to lookup a field based upon 2 items. I am stumped on how to
accomplish this. VLookup requires only 1 criteria. I tried creating an array formula {=VLOOKUP(B27:B28,A23:C25,3)}. It resulted in contract c, but if i change the criteria data to 1/1/09... the result did not change. Below is an example. Table: 10000 1/1/2008 contract a 30000 1/1/2009 contract b 30000 1/1/2010 contract c Criteria: contract# 30000 date 1/1/2010 Result --- contract c |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mutliple criteria vlookups
Chip Pearson has a good article on understanding array formulas for future
reference. http://www.cpearson.com/excel/ArrayFormulas.aspx Try: (Where D1 and D2 are your criteria cells) =INDEX($A$23:$C$25,MATCH($D$1&$D$2,$A$23:$A$25&$B$ 23:$B$25,0),3) Entered as an array with ctrl+shift+enter Does that do what you're looking for? "Squid" wrote: I need a way to lookup a field based upon 2 items. I am stumped on how to accomplish this. VLookup requires only 1 criteria. I tried creating an array formula {=VLOOKUP(B27:B28,A23:C25,3)}. It resulted in contract c, but if i change the criteria data to 1/1/09... the result did not change. Below is an example. Table: 10000 1/1/2008 contract a 30000 1/1/2009 contract b 30000 1/1/2010 contract c Criteria: contract# 30000 date 1/1/2010 Result --- contract c |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mutliple criteria vlookups
If your table had headers and occupied A1:C4 as in:
number date contract 10000 1/1/2008 contract a 30000 1/1/2009 contract b 30000 1/1/2010 contract c And your criteria had headers and occupied A7:B8 as in: number date 30000 1/1/2009 Then you could use DGET as in: =DGET(A1:C4,"contract",A7:B8) ....to get whatever contract you want from the table. HTHs. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mutliple criteria vlookups
Hi,
Here is one of many ways =INDEX(C23:C25,MATCH(B27&B28,A23:A25&B23:B25,0)) This is an array entered formula so you need to press Shift+Ctrl+Enter to enter it, not Enter. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Squid" wrote: I need a way to lookup a field based upon 2 items. I am stumped on how to accomplish this. VLookup requires only 1 criteria. I tried creating an array formula {=VLOOKUP(B27:B28,A23:C25,3)}. It resulted in contract c, but if i change the criteria data to 1/1/09... the result did not change. Below is an example. Table: 10000 1/1/2008 contract a 30000 1/1/2009 contract b 30000 1/1/2010 contract c Criteria: contract# 30000 date 1/1/2010 Result --- contract c |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mutliple criteria vlookups
Yes, this was exactly what I was looking for. Thanks! :)
"~L" wrote: Chip Pearson has a good article on understanding array formulas for future reference. http://www.cpearson.com/excel/ArrayFormulas.aspx Try: (Where D1 and D2 are your criteria cells) =INDEX($A$23:$C$25,MATCH($D$1&$D$2,$A$23:$A$25&$B$ 23:$B$25,0),3) Entered as an array with ctrl+shift+enter Does that do what you're looking for? "Squid" wrote: I need a way to lookup a field based upon 2 items. I am stumped on how to accomplish this. VLookup requires only 1 criteria. I tried creating an array formula {=VLOOKUP(B27:B28,A23:C25,3)}. It resulted in contract c, but if i change the criteria data to 1/1/09... the result did not change. Below is an example. Table: 10000 1/1/2008 contract a 30000 1/1/2009 contract b 30000 1/1/2010 contract c Criteria: contract# 30000 date 1/1/2010 Result --- contract c |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mutliple criteria vlookups
Try this array formula** :
=INDEX(C23:C25,MATCH(1,(A23:A25=B27)*(B23:B25=B28) ,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Squid" wrote in message ... I need a way to lookup a field based upon 2 items. I am stumped on how to accomplish this. VLookup requires only 1 criteria. I tried creating an array formula {=VLOOKUP(B27:B28,A23:C25,3)}. It resulted in contract c, but if i change the criteria data to 1/1/09... the result did not change. Below is an example. Table: 10000 1/1/2008 contract a 30000 1/1/2009 contract b 30000 1/1/2010 contract c Criteria: contract# 30000 date 1/1/2010 Result --- contract c |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting mutliple ranges | Excel Worksheet Functions | |||
mutliple conditions | Excel Discussion (Misc queries) | |||
Dependent vlookups - nested vlookups (maybe) | Excel Worksheet Functions | |||
repost: formula qhich checks mutliple criteria before counting | Excel Discussion (Misc queries) | |||
Mutliple Criteria Count function | Excel Worksheet Functions |