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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Will the value of I83 only exist in one of those two sheets?
 
Upvote 0
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"))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top