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

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
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.