Lookup of a value on multiple sheets?

[Insomnia81]

I can figure this out with 1 sheet, but I’m at a complete loss with an OR function.

So I have a Name in Cell A1 on Sheet1. I want to see if that Name is in column A on Sheet2 or Sheet3 and return the text “Sheet2” if it’s on Sheet2 or the text “Sheet3” if found on Sheet3.

Thanks!

 

[Scott T]

Try

=IF(NOT(ISNA(VLOOKUP(A1,Sheet2!A1:A18,1,0))),"Sheet2",IF(NOT(ISNA(VLOOKUP(A1,Sheet3!A1:A18,1,0))),"Sheet3","not on Sheet2 or Sheet3"))

 

[Special-K99]

Here's one way

=LOOKUP(NOT(ISNA(MATCH(A1,Sheet2!A1:A1000,0)))+NOT(ISNA(MATCH(A1,Sheet3!A1:A1000,0)))*2,{0,1,2,3},{"Not Found","Sheet 2", "Sheet 3", "Sheets 2 and 3"})