# IF and IFS not working properly

I am trying to locate data present in different sheets. I have tried using `IF` and `IFS`.
Below are the formulas I have used. For `IF`, I get `NOT IN USE` even when I know there should be one of the sheets and I get `NA` for `IFS` except 2 cell which gives the correct answer.
What am I doing wrong or is there any other way for it.

``=IFS(B2=Casula!\$A:\$A,"CASULA",B2=Miranda!\$A:\$A,"MIRANDA",B2=Parramatta!\$A:\$A,"PARRA",B2=Leichhardt!\$A:\$A,"LEICHHARDT",B2=Penrith!\$A:\$A,"PENRITH",B2=Bradbury!\$A:\$A,"BRADBURY",B2=Revesby!A:A,"REVESBY",B2=Darlinghurst!\$A:\$A,"DARLING",B2=StMarys!A:A,"STMARYS",B2=SurryHills!\$A:\$A,"SURRY")``

``=IF(B2=Casula!\$A:\$A,"Casula",IF(B2=Miranda!\$A:\$A,"Miranda",IF(B2=Parramatta!\$A:\$A,"Parra",IF(B2=Leichhardt!\$A:\$A,"Leich","NOT IN USE"))))``

Sample sheet

``````=ARRAYFORMULA(IF(LEN(B2:B),
IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, Casula!A:A)),       "Casula",
IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, Miranda!A:A)),      "Miranda",
IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, Parramatta!A:A)),   "Parra",
IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, Leichhardt!A:A)),   "Leich",
IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, Penrith!A:A)),      "Penrith",
IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, Revesby!A:A)),      "Revesby",
IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, Darlinghurst!A:A)), "Darling",
IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, StMarys!A:A)),      "Marys",
IF(REGEXMATCH(B2:B, TEXTJOIN("|", 1, SurryHills!A:A)),   "SurryHills",
"NOT IN USE")))))))))), ))`````` Alternative 1

Instead of using comparison like `B2=Casula!\$A:\$A` use `NOT(ISNA(MATCH(B2,Casula!\$A\$2:\$A,0)))`.

Example

`=IF(NOT(ISNA(MATCH(B2,Casula!\$A\$2:\$A,0))),"Casula",IF(NOT(ISNA(MATCH(B2,Miranda!\$A\$2:\$A,0))),"Miranda",IF(NOT(ISNA(MATCH(B2,Parramatta!\$A\$2:\$A,0))),"Parra",IF(NOT(ISNA(MATCH(B2,Leichhardt!\$A\$2:\$A,0))),"Leich","NOT IN USE"))))`

NOTE: Using long chain of formulas could make the spreadsheet recalculation slower. It depends on how complex is the formula and how many formulas the spreadsheet has.

One of the things that "you are doing wrong" is that your formulas use ranges of the form `A:A` on "regular" `IF` / `IFS` functions. To make it clear lets take one of the comparisons

``````B2=Casula!\$A:\$A
``````

This comparison isn't comparing the value of `B2` with all the values on `Casula!\$A:\$A`. If the above comparison is on row 2, the value of `B2` is compared only with `Casula!A2`. To compare it with all the values, we could use `ARRAYFORMULA` but then the comparison will return an array of values. Some times this is an advantage because it helps us avoid large formula chains which slow the spreadsheet recalculation but in this case limiting us to add `ARRAYFORMULA` will not do what the OP is looking for.

`MATCH` looks for a value on a list of values. If it's found return the position of this value otherwise return `#N/A`. The formula on the alternative 1 use `ISNA` to test if the MATCH returned `#N/A` and `NOT` to "inverse" from `TRUE` to `FALSE` and vice-versa.

For details on how each function works please checkout the G Suite Document Editors Help Center.

There are other alternatives like the one depicted on the previous answer. The best one depends on the spreadsheet owner/editor. IMHO, first look for something easier to understand and maintain, then look for something that has the least impact on spreadsheet performance.