First I'll explain what I already have (which works), then explain my question, which is about expanding it:

I have two different spreadsheets that will be worked on at the same time, I needed to see if a given value has already been entered in the other as it may change the outcome.


If I'm working on Spreadsheet 1 and enter a value into A1 i want it to search spreadsheet 2 to see if it is anywhere in column A. If yes I go down 1 path, if no I go down another.

The formula I am currently using is this:

=IFERROR(IF(MATCH(A1,'[Spreadsheet 2.xlsx]Sheet1'!$A:$A,0),"Yes",),"")

This does what I want, if the value in spreadsheet 1, A1 appears anywhere in spreadsheet 2, column A, I get 'Yes' if it doesn't the space is left blank.

However I now need to find out if the value in spreadsheet 1, A1 is in spreadsheet 2, column A or Spreadsheet 3, Column A.

I don't need to know where it occurs, thus 'Yes' or blank, I just need to know if it appears anywhere in column A in Spreadsheet 2 or 3.

I currently have this formula, but it isn't working (it doesn't trigger an error, it just doesn't do what I need it to:

=IFERROR(IF(MATCH(A1,'[Spreadsheet 2.xlsx]Sheet1'!$A:$A,0),"Yes", IF(MATCH(A1,'[Spreadsheet 3.xlsx]Sheet1'!$A:$A,0),"Yes",)),"")

This works if there is a match in Spreadsheet 2, however not if there is a match only in spreadsheet 3.

I'm sure I'm just doing something stupid, but I can't see it.

Can anyone help?


Try using the ISERROR function instead. It's not a succinct as IFERROR but it's easier to follow when you start nesting conditions.

=IF(AND(ISERROR(MATCH(A1,'[Spreadsheet 2.xlsx]Sheet1'!$A:$A,0)), ISERROR(MATCH(A1,'[Spreadsheet 3.xlsx]Sheet1'!$A:$A,0))),"","YES")

If both match functions return an error, you get "". If either returns a non-error, then AND returns FALSE and your result is YES. This gets a little unwieldy if you want to check a lot of sheets instead of just a few other sheets.

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.