lookup across 2 columns

amaru96
amaru96 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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")

Commented:
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

Commented:
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")
Commented:
sorry, change that to this for your case:

B1: =IF(AND(ISNA(VLOOKUP(A1,C:C,1,FALSE)),ISNA(VLOOKUP(A1,D:D,1,FALSE))),"Does not exist","Exists")

Author

Commented:
Excellent, works great! Thanks rspahitz.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial