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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,053
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
9,230
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,293
Messages
5,449,497
Members
405,569
Latest member
deanro

This Week's Hot Topics

Top