IF ERROR match formula - trying to replace #NA with blank. Formula worked fine until I added a search in another worksheet :(

[jarem]

I had a simple formula that worked fine - I had a cell that I wanted to search another worksheet, and if it found a match, populate the text in that cell:
=IFERROR(INDEX(Sheet1!$A:$A,MATCH(B16,Sheet1!$G:$G,0)),””)

But then I needed it to look in two separate worksheets and populate the cell if there is a match:
=IFERROR(INDEX(Sheet1!$A:$A,MATCH(B16,Sheet1!$G:$G,0)),INDEX(Sheet2!$A:$A,MATCH(B16,Sheet2!$B:$B,0)))

I know this must be simple but I can't figure it out - I can no longer add in the "" to have it be blank if there is no match returned & now I get an #NA error.

Any suggestions would be greatly appreciated!!!

 

[Fluff]

Hi & welcome to MrExcel.
Do you want to return the value from sheet1 if it exists & if it doesn't exist search sheet2?

 

[jarem]

Yes, that is right. And if nothing is in either place, for it to return a blank

In an ideal world, there should never been two matches in both places - so I wouldn't mind an error message if there were matches in both but I'd be happy to fix the #NA error

 

[Fluff]

Ok, how about

=IFNA(INDEX(Sheet1!$A:$A,MATCH(B16,Sheet1!$G:$G,0)),IFNA(INDEX(Sheet2!$A:$A,MATCH(B16,Sheet2!$B:$B,0)),""))

 

[jarem]

This fixed it!! Thank you soooo much, I spend an embarrassing amount of time trying to figure that out on my own. Much appreciated!

 

[Fluff]

You're welcome & thanks for the feedback.