vlookup using two lookup values?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tjb - 21 Nov 2005 22:41 GMT

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.

Reply to this Message

Bob Phillips - 21 Nov 2005 22:48 GMT

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

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

Signature

HTH

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

> 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.

Reply to this Message

Dave R. - 21 Nov 2005 22:51 GMT

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

> =INDEX(Sheet2!C1:C1000,MATCH(A1&B1,Sheet2!A1:A1000&Sheet2!B1:B1000,0))
>
[quoted text clipped - 7 lines]
> like
> > to not have to do the concatenate step.

Reply to this Message

Dave R. - 21 Nov 2005 22:53 GMT

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

> Cool, something more intuitive. Have not seen that version before..
>
[quoted text clipped - 10 lines]
> > like
> > > to not have to do the concatenate step.

Reply to this Message

Bob Phillips - 21 Nov 2005 23:14 GMT

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

Bob

> I see now, because they're doing different things. :)
>
[quoted text clipped - 13 lines]
> > > like
> > > > to not have to do the concatenate step.

Reply to this Message

Dave R. - 21 Nov 2005 23:29 GMT

So they do, pardon me.

> How do you mean? They are accomplishing the same thing surely?
>
[quoted text clipped - 18 lines]
> > > > like
> > > > > to not have to do the concatenate step.

Reply to this Message

Dave R. - 21 Nov 2005 22:50 GMT

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

=INDEX(I17:I19,MATCH(1,(G17:G19="dog")*(H17:H19="cat"),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.

Reply to this Message

James - 22 Nov 2005 11:53 GMT

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

Reply to this Message

Roger Govier - 22 Nov 2005 12:23 GMT

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

> I posted this earlier but here it is again...
>
[quoted text clipped - 19 lines]
>>succcess using concatenate and then using vlookup on that cell but i'd like
>>to not have to do the concatenate step.

Reply to this Message

James - 25 Nov 2005 04:43 GMT

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

> Hi James
>
[quoted text clipped - 30 lines]
> >>succcess using concatenate and then using vlookup on that cell but i'd like
> >>to not have to do the concatenate step.

Reply to this Message

Krishnakumar - 25 Nov 2005 05:21 GMT

Try iN C10,

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

Normal Enter.

HTH

Signature

Krishnakumar

Reply to this Message