# Can I (and If so How?) use the MATCH function to search two (or more) different spreadsheets for a value?

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.

ie.

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.