asked on 5/16/2011

lookup across 2 columns

Hi guys, I'm trying to lookup a value in a cell against 2 columns and if it exists to return "exists" and if not "does not exist" but having much luck.

To try and clarifiy, below is what I'm trying to get to, where columnB returns the result:

ColumnA        ColumnB             ColumnD       ColumnE
Jack        Exists             Nick        Daniel
Michael        Does not exist       John        Jack
Jenny        Exists             Jenny        Bill
Peter        Does not exist Lindy        Luke
Paul        Does not exist       Sophie        Sam

Microsoft Excel

Last Comment

amaru96

5/16/2011

rspahitz

5/16/2011

What I typically do in cases like this is to add a "dummy" column to handle the lookup.

In this case, you can use column F with this formula:

F1: =D1
F2: =D2
...
Fn: =Dn
F(n+1): =E1
F(n+2): =E2
...

then your formula in column B is:

B1: =If(ISNA(VLOOKUP(A1, F:F, 1, false)),"Does not exist", "Exists")

sshah254

5/16/2011

There's no way to do this with a VLOOKUP function.

If you are willing to do this in VBA, then that is possible.

Ss

rspahitz

5/16/2011

You can try this by combining two vlookups:

=IF(AND(ISNA(VLOOKUP(C1,A:A,1,FALSE)),ISNA(VLOOKUP(C1,B:B,1,FALSE))),"Does not exist","Exists")

ASKER CERTIFIED SOLUTION

rspahitz

5/16/2011