asked on

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

rspahitz

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

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amaru96

ASKER

Excellent, works great! Thanks rspahitz.