VLookup Advice

  steviegee 13:17 25 Jul 2008
Locked

Is it possible to look at 2 fields whilst using Vlookup? I want something to look at one cell then another and if they match put an entry in. Thanks.

  nosharpe 14:09 25 Jul 2008

Can you give an example?

  silverous 14:11 25 Jul 2008

I'm not 100% clear, do you mean use two fields to lookup based on 2 values in a lookup table?

AS an example say you had a list of first names and surnames, and you want to look up their phone number from a lookup table. You can't lookup based on first name because there are, say 2 pauls, so you need to lookup based on both first name and surname?

What I tend to do in these situations is to created a "contcatenated key" to lookup.

So in my lookup table I'll add a column to the left with a formula which combines both columns (I usually add something in the middle,like a dash not sure why but I do!)

So if column A is first name, column B is surname, in column C add:

=A1&"-"&B1

which will give you e.g.

John-Smith

once that is in your lookup table, when you are doing the lookup you simply do a similar concatenation e.g.

vlookup(A1&"-"&B1,<other parameters>)

If you see what I mean?

  steviegee 14:21 25 Jul 2008

If Al has John and B1 has 1.6 09/78- for example I want to vlookup in C1 Mary that I have in another table. Make sense? lol

  silverous 16:25 25 Jul 2008

no not at all, you haven't told us what you are vlookup'ing based on or what's in your other table other than what you want to find...we need more info to help.

  silverous 16:27 25 Jul 2008

To do a vlookup you need to lookup based on a key - so in the example you gave if Mary is John's wife (say) then in your other table you'd have:

A1 B1
John Mary

and in C1 you'd do:

VLOOKUP(A1,<other table>,2,FALSE)

You've included what's in B1 but without any reference to what it is / means.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Add Depth Of Field to a photo using Tilt Shift Blur in Photoshop

iPhone tips & tricks

Les meilleures tablettes 2017