Can you use 2 look up values in VLookup?

[Guest]

Is it possible to use 2 look up values in VLookup? Or is there a different
method I should be using?

For example:

Kitchen Bath Laundry
Model A Price A Price B Price C
Model B Price D Price E Price F

I have the model stored in cell A1 and the room stored in cell B1. I want
to look up the model (in cell A1) and the room (in cell B2) in the table
array (above) and return the appropriate price. If I have Model A and
Kitchen, I want VLookup to return Price A. If I have Model A and Bath I want
VLookup to return Price B and so on.

Thank you.

 

[Bob Phillips]

=INDEX(A13,MATCH("Model A",A1:A3,0),MATCH("Bath",A11,0))

as an example

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Richard Buttrey]

Is it possible to use 2 look up values in VLookup? Or is there a different
method I should be using?

For example:

Kitchen Bath Laundry
Model A Price A Price B Price C
Model B Price D Price E Price F

I have the model stored in cell A1 and the room stored in cell B1. I want
to look up the model (in cell A1) and the room (in cell B2) in the table
array (above) and return the appropriate price. If I have Model A and
Kitchen, I want VLookup to return Price A. If I have Model A and Bath I want
VLookup to return Price B and so on.

Thank you.

I think there's a small typo. I guess you meant B1 in both references.

With Model and Room in A1 & B1 and the table above in A57

use

=INDEX(B67,MATCH(A1,A6:A7),MATCH(B1,B55))

HTH




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[BenjieLop]

Try this ...

=VLOOKUP(E1,$A$1:$D$50,MATCH(E2,$A$1:$D$50,0),0

where:

cell E1 contains your Model Number (Model A, Model B ... whatever)
cell E2 contains your room (Bath, Kitchen. Laundry... whatever)
$A$1:$D$50 is the assumed range of your table.

Hope this will help you.

Regards.

 

[Guest]

This did not work. I'm getting the value of the first Match returned. Can I
e-mail you my spreadsheet? I think the problem might be that the cell for
the 2nd matches look up value gets its value based on a VLookup.

Thanks.

 

[Bob Phillips]

What do you want if not the first match?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

I want the price that corresponds with Model/Room.

 

[Guest]

I get #N/A

Try this ...

=VLOOKUP(E1,$A$1:$D$50,MATCH(E2,$A$1:$D$50,0),0)

where:

cell E1 contains your Model Number (Model A, Model B ... whatever)
cell E2 contains your room (Bath, Kitchen. Laundry... whatever)
$A$1:$D$50 is the assumed range of your table.

Hope this will help you.

Regards.

 

[Guest]

Here's how I finally got it to work:

=INDEX('Data Table'!$Y$6:$AI$23, MATCH($B$5,'Data Table'!$Y$6:$Y$23,),
MATCH($A$13,'Data Table'!$Y$6:$AI$6,)). I used the Lookup wizard to get it
to populate the formula and then I changed the MATCH lookup value arguments.

Thanks for your help!

 

[Guest]

Here's how I finally got it to work:

=INDEX('Data Table'!$Y$6:$AI$23, MATCH($B$5,'Data Table'!$Y$6:$Y$23,),
MATCH($A$13,'Data Table'!$Y$6:$AI$6,)). I used the Lookup wizard to get it
to populate the formula and then I changed the MATCH lookup value arguments.

Thanks for your help!

 

[BenjieLop]

My apologies for a typo. The correct formula is

=VLOOKUP(E1,$A$1:$D$50,MATCH(E2,$A$1:$D$1,0),0

with the correction in RED.

BTW, this is just one formula you can use. Another one you can use i
an INDEX/MATCH formula (which you found to be already working).

Regards and again, I apologize for sending you an earlier formula wit
a typo.

I get #N/A