Is there a way to perform a vlookup with two lookup values? For instance,
I'd like to look up the value of A1 and B1 in a table with A1 and B1 values
included to return cell C2 from the table. Any ideas? I've had limited
succcess using concatenate and then using vlookup on that cell but i'd like
to not have to do the concatenate step.

=INDEX(Sheet2!C1:C1000,MATCH(A1&B1,Sheet2!A1:A1000 &Sheet2!B1:B1000,0))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tjb" wrote in message
...
Is there a way to perform a vlookup with two lookup values? For instance,
I'd like to look up the value of A1 and B1 in a table with A1 and B1
values
included to return cell C2 from the table. Any ideas? I've had limited
succcess using concatenate and then using vlookup on that cell but i'd
like
to not have to do the concatenate step.

You can accomplish this with index/match which works similarly to vlookup.

=INDEX(I17:I19,MATCH(1,(G17:G19="dog")*(H17:H19="c at"),0))

enter this as an array formula (use CTRL-shift-enter when entering the
formula).

This will pull the matching value from column I where columns G and H match
"dog" and "cat" respectively.



"tjb" wrote in message
...
Is there a way to perform a vlookup with two lookup values? For instance,
I'd like to look up the value of A1 and B1 in a table with A1 and B1
values
included to return cell C2 from the table. Any ideas? I've had limited
succcess using concatenate and then using vlookup on that cell but i'd
like
to not have to do the concatenate step.

Cool, something more intuitive. Have not seen that version before..


"Bob Phillips" wrote in message
...
=INDEX(Sheet2!C1:C1000,MATCH(A1&B1,Sheet2!A1:A1000 &Sheet2!B1:B1000,0))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tjb" wrote in message
...
Is there a way to perform a vlookup with two lookup values? For
instance,
I'd like to look up the value of A1 and B1 in a table with A1 and B1
values
included to return cell C2 from the table. Any ideas? I've had limited
succcess using concatenate and then using vlookup on that cell but i'd
like
to not have to do the concatenate step.

I see now, because they're doing different things. :)


"Dave R." wrote in message
...
Cool, something more intuitive. Have not seen that version before..


"Bob Phillips" wrote in message
...
=INDEX(Sheet2!C1:C1000,MATCH(A1&B1,Sheet2!A1:A1000 &Sheet2!B1:B1000,0))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tjb" wrote in message
...
Is there a way to perform a vlookup with two lookup values? For
instance,
I'd like to look up the value of A1 and B1 in a table with A1 and B1
values
included to return cell C2 from the table. Any ideas? I've had
limited
succcess using concatenate and then using vlookup on that cell but i'd
like
to not have to do the concatenate step.

How do you mean? They are accomplishing the same thing surely?

Bob


"Dave R." wrote in message
...
I see now, because they're doing different things. :)


"Dave R." wrote in message
...
Cool, something more intuitive. Have not seen that version before..


"Bob Phillips" wrote in message
...
=INDEX(Sheet2!C1:C1000,MATCH(A1&B1,Sheet2!A1:A1000 &Sheet2!B1:B1000,0))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tjb" wrote in message
...
Is there a way to perform a vlookup with two lookup values? For
instance,
I'd like to look up the value of A1 and B1 in a table with A1 and B1
values
included to return cell C2 from the table. Any ideas? I've had
limited
succcess using concatenate and then using vlookup on that cell but
i'd
like
to not have to do the concatenate step.

So they do, pardon me.


"Bob Phillips" wrote in message
...
How do you mean? They are accomplishing the same thing surely?

Bob


"Dave R." wrote in message
...
I see now, because they're doing different things. :)


"Dave R." wrote in message
...
Cool, something more intuitive. Have not seen that version before..


"Bob Phillips" wrote in message
...

=INDEX(Sheet2!C1:C1000,MATCH(A1&B1,Sheet2!A1:A1000 &Sheet2!B1:B1000,0))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tjb" wrote in message
...
Is there a way to perform a vlookup with two lookup values? For
instance,
I'd like to look up the value of A1 and B1 in a table with A1 and
B1
values
included to return cell C2 from the table. Any ideas? I've had
limited
succcess using concatenate and then using vlookup on that cell but
i'd
like
to not have to do the concatenate step.

I posted this earlier but here it is again...

Assuming City is keyed in into cell "A1"

City State Miles
Lexington NC 423
Nashville TN 501
Lexington KY 354
Hickory NC 645
Bristol TN 344
Lexington TN 233

City State
Lexington KY 354 = Answer Cell "C10"

Key in the following formula into Cell "C10"
=VLOOKUP(A10:B10,A2:C7,3) then enter Crtl+Shift+Enter


"tjb" wrote:

Is there a way to perform a vlookup with two lookup values? For instance,
I'd like to look up the value of A1 and B1 in a table with A1 and B1 values
included to return cell C2 from the table. Any ideas? I've had limited
succcess using concatenate and then using vlookup on that cell but i'd like
to not have to do the concatenate step.

Hi James

That doesn't work. It returns 354 no matter whether you enter NC, KY or TN
in cell B10.
Even adding the false fourth argument doesn't help. There is no way that
Vlookup will work in this manner without doing a concatenation of cells.

Regards

Roger Govier


James wrote:
I posted this earlier but here it is again...

Assuming City is keyed in into cell "A1"

City State Miles
Lexington NC 423
Nashville TN 501
Lexington KY 354
Hickory NC 645
Bristol TN 344
Lexington TN 233

City State
Lexington KY 354 = Answer Cell "C10"

Key in the following formula into Cell "C10"
=VLOOKUP(A10:B10,A2:C7,3) then enter Crtl+Shift+Enter


"tjb" wrote:


Is there a way to perform a vlookup with two lookup values? For instance,
I'd like to look up the value of A1 and B1 in a table with A1 and B1 values
included to return cell C2 from the table. Any ideas? I've had limited
succcess using concatenate and then using vlookup on that cell but i'd like
to not have to do the concatenate step.

Darn! you are right. My worksheet is also having the problem. Let me see if i
can refine it. Thanks for the update.

"Roger Govier" wrote:

Hi James

That doesn't work. It returns 354 no matter whether you enter NC, KY or TN
in cell B10.
Even adding the false fourth argument doesn't help. There is no way that
Vlookup will work in this manner without doing a concatenation of cells.

Regards

Roger Govier


James wrote:
I posted this earlier but here it is again...

Assuming City is keyed in into cell "A1"

City State Miles
Lexington NC 423
Nashville TN 501
Lexington KY 354
Hickory NC 645
Bristol TN 344
Lexington TN 233

City State
Lexington KY 354 = Answer Cell "C10"

Key in the following formula into Cell "C10"
=VLOOKUP(A10:B10,A2:C7,3) then enter Crtl+Shift+Enter


"tjb" wrote:


Is there a way to perform a vlookup with two lookup values? For instance,
I'd like to look up the value of A1 and B1 in a table with A1 and B1 values
included to return cell C2 from the table. Any ideas? I've had limited
succcess using concatenate and then using vlookup on that cell but i'd like
to not have to do the concatenate step.

Try iN C10,

=LOOKUP(2,1/((A2:A7=A10)*(B2:B7=B10)),C2:C7)

Normal Enter.

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=487029