VLookup Advice

  steviegee 13:17 25 Jul 08
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 08

Can you give an example?

  silverous 14:11 25 Jul 08

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 08

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 08

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 08

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.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac