# How do I compare a cell value with a range of values on 2 dirrerent sheets and return a Text I specify

In Excel I have 3 sheets in the same workbook.

• I want to compare column A on "Sheet 1" with Column B on "Sheet 2" and Column b on "Sheet 3".
• If I find an exact match on anywhere in column B "Sheet 2" I want cell B next to the compared value on "Sheet 1" column A to say "Sheet 2"
• If I find an exact match on anywhere in column B "Sheet 3 I want cell B next to the compared value on "Sheet 1" column A to say "Sheet 3"

The following formula works perfectly to compare only sheet 2 but I cannot figure out how to modify it to work and compare values on both sheet 2 and sheet 3.

``````=IF(ISERROR(MATCH(A2,'Sheet 2'!\$b\$2:\$b\$30,FALSE)),"","Sheet 2")
``````

Please see the images below.

Current results:

End results:

use name manager and give a name to

``````=IF(ISERROR(MATCH(A2,'Sheet 2'!\$b\$2:\$b\$30,FALSE)),"","Sheet 2")
``````

as SHT_2 and then give a name to

``````=IF(ISERROR(MATCH(A2,'Sheet 3'!\$b\$2:\$b\$30,FALSE)),"","Sheet 3")
``````

as SHT_3

then a final formula of

``````if(SHT_2="",SHT_3,SHT_2)
``````

as SHT

then in you B column just enter the `=SHT` formula

in place of `""` put another formula:

``````=IF(ISERROR(MATCH(A2,'Sheet 2'!\$b\$2:\$b\$30,FALSE)),IF(ISERROR(MATCH(A2,'Sheet 3'!\$b\$2:\$b\$30,FALSE)),"","Sheet 3"),"Sheet 2")
``````

So when no match is found on sheet 2 it will look at sheet 3. If a match is found on sheet 3 then it will return "sheet 3". If not it will return the blank cell.

• While this may answer the question, it would be a better answer if you could provide some explanation why it does so. – DavidPostill Jun 2 '16 at 22:14