How to look for a value across two tables and return result of which table the result was found in

GSElo

New Member
Joined
Sep 12, 2019
Messages
2
Long time reader, first time caller,

I want to look for a value in two tables(columns in sperate worksheets) and have the result come back of which table the value was found in.

I have tried with If and Vlookup, and can return the result of the first table search, but does not work when searching the second table.

=IF(VLOOKUP(I83,Rebuild!I:J,1,FALSE)=I83,"Rebuild",IF(VLOOKUP(I83,Retired!I:J,1,FALSE)=I83,"Retired"))


Any help would much appreciated.

Gary
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,717
Office Version
365
Platform
Windows
Will the value of I83 only exist in one of those two sheets?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,371
Office Version
2019
Platform
Windows
If I83 will definitely be in one of the 2 sheets then try

=IF(ISNUMBER(MATCH(I83,Rebuild!I:I,0)),"Rebuild","Retired")

If I83 might not be in either then

=IF(ISNUMBER(MATCH(I83,Rebuild!I:I,0)),"Rebuild",IF(ISNUMBER(MATCH(I83,Retired!I:I,0)),"Retired","No Match"))
 

GSElo

New Member
Joined
Sep 12, 2019
Messages
2
Thank you!
I83 might not be there, and the second solution worked perfectly.

Can see that I was quite some way off in my original formula!

Thanks again for the help
 

Forum statistics

Threads
1,085,423
Messages
5,383,592
Members
401,838
Latest member
mydog

Some videos you may like

This Week's Hot Topics

Top