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.

=INDEX(A1:D3,MATCH("Model A",A1:A3,0),MATCH("Bath",A1:D1,0))

as an example

--

HTH

Bob Phillips

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

"dread" wrote in message
...
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.

On Mon, 26 Jun 2006 09:43:01 -0700, dread
wrote:

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 A5:D7

use

=INDEX(B6:D7,MATCH(A1,A6:A7),MATCH(B1,B5:D5))

HTH




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Try this ...

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

whe

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.



dread Wrote:
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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=555674

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" wrote:

=INDEX(A1:D3,MATCH("Model A",A1:A3,0),MATCH("Bath",A1:D1,0))

as an example

--

HTH

Bob Phillips

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

"dread" wrote in message
...
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.

What do you want if not the first match?

--

HTH

Bob Phillips

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

"dread" wrote in message
...
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" wrote:

=INDEX(A1:D3,MATCH("Model A",A1:A3,0),MATCH("Bath",A1:D1,0))

as an example

--

HTH

Bob Phillips

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

"dread" wrote in message
...
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 want the price that corresponds with Model/Room.

"Bob Phillips" wrote:

What do you want if not the first match?

--

HTH

Bob Phillips

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

"dread" wrote in message
...
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" wrote:

=INDEX(A1:D3,MATCH("Model A",A1:A3,0),MATCH("Bath",A1:D1,0))

as an example

--

HTH

Bob Phillips

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

"dread" wrote in message
...
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 get #N/A

"BenjieLop" wrote:


Try this ...

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

whe

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.



dread Wrote:
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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=555674

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" wrote:


Try this ...

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

whe

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.



dread Wrote:
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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=555674

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!


"Bob Phillips" wrote:

=INDEX(A1:D3,MATCH("Model A",A1:A3,0),MATCH("Bath",A1:D1,0))

as an example

--

HTH

Bob Phillips

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

"dread" wrote in message
...
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.

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 is
an INDEX/MATCH formula (which you found to be already working).

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


dread Wrote:
I get #N/A

"BenjieLop" wrote:


Try this ...

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

whe

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.



dread Wrote:
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.


--
BenjieLop



------------------------------------------------------------------------
BenjieLop's Profile:
http://www.excelforum.com/member.php...o&userid=11019
View this thread:
http://www.excelforum.com/showthread...hreadid=555674




--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=555674