0

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

0

=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, Bradbury!A:A)),     "Bradbury",
 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")))))))))), ))

0

spreadsheet demo

0

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.

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.