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

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

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.

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

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

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