IFERROR Help (if column b in spreadsheet 1 matches column b in spreadsheet 2, return column E from spreadsheet 2)

[notanexpertinexcel]

Here's my formula:

=IFERROR(IF(ISNA(VLOOKUP(B2,'[Master Body Site.xlsx]Sheet1'!$B:$E,'[Master Body Site.xlsx]Sheet1'!$B:$B,FALSE)),"",VLOOKUP(C2,'[Master Body Site.xlsx]Sheet1'!$C:$C,'[Master Body Site.xlsx]Sheet1'!$C:$C+'[Master Body Site.xlsx]Sheet1'!$E:$E,FALSE)),"")

I am trying to look up values in my working spreadsheet from column B, compare them to column B in my Master spreadsheet. If there is a match, please return the value from Column E in my Master spreadsheet.

If No match on that search, then I want to perform the same lookup using Column C from working spreadsheet, lookup in column C in Master spreadsheet, and return column E from my Master spreadsheet.

If No match, leave it blank.

Any Help is greatly appreciated!

 

[dispelthemyth]

Try the below

=IF(COUNTIF('[Master Body Site.xlsx]Sheet1'!$B:$B,B2)>0,INDEX('[Master Body Site.xlsx]Sheet1'!$E:$E,MATCH(B2,'[Master Body Site.xlsx]Sheet1'!$B:$B,0),1),IF(COUNTIF('[Master Body Site.xlsx]Sheet1'!$C:$C,C2)>0,INDEX('[Master Body Site.xlsx]Sheet1'!$E:$E,MATCH(C2,'[Master Body Site.xlsx]Sheet1'!$C:$C,0),1),"Not in column B or C"))

 

[notanexpertinexcel]

It does work, however, it is returning incorrect values for many lookups. I have about 3000 rows in this spreadsheet, and have 27 spreadsheets to update. I am hoping to use this spreadsheet as a master and then perform lookups against it for the other 26. Your help is greatly appreciated. Any chance I can send you a sample?

 

[notanexpertinexcel]

Nevermind, and thank you! I found that I had copied the formula incorrectly. I autofilled and it works wonderfully! Thank you for the quick response! Mr. Excel community is awesome!