0

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:

enter image description here

End results:

enter image description here

Improve this question

2 Answers 2

Reset to default
0

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

Improve this answer
0

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.

Improve this answer
1
  • 1
    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, 2016 at 22:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .