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