CODEWITHSUNDEEP
if-statementexcel-formula
William Fortin
William Fortin

Reputation: 15

Excel formula INDIRECT and IF - Return all matches from multiple rows

here is my code used to list objects (Sheet3!A2:A5) that have an attribute (Sheet3!E2:M5) matching the attribute in A2 of the current sheet. Right now, the formula returns only the first object that matches the attribute, but does not list the others. For example, if A2 = "Yellow" and the object "Banana" (Sheet3!A2) has "Yellow" listed in E2 and the object "Lemon" (Sheet3!A5) has "Yellow" listed in M5, there would be two objects with the attribute "Yellow", but my formula only returns the cell A2, because it is encountered before A5. Do you know how I can improve my code so it returns every object that match the attribute?

=IF(ISNUMBER(MATCH(B2; Sheet3!E2:M2; 0)); INDIRECT("Sheet3!A2"); IF(ISNUMBER(MATCH(B2; Sheet3!E3:M3; 0)); INDIRECT("Sheet3!A3"); IF(ISNUMBER(MATCH(B2; Sheet3!E4:M4; 0)); INDIRECT("Sheet3!A4"); IF(ISNUMBER(MATCH(B2; Sheet3!E5:M5; 0)); INDIRECT("Sheet3!A5");""))))

Thanks in advance,

*The examples are fictional, and in reality, the code contains many more rows; I simplified the example for the sake of clarity.

Upvotes: 0

Views: 34

Answers (1)

Tom
Tom

Reputation: 11

If you are on the latest version of Excel, the combination of the FILTER() function, along with BYROW() and LAMBDA() will give you the result you are looking for.

The following function will give you the list of all the column A items matching the attribute.

=IFERROR(
         FILTER(Sheet3!A2:A5,
                BYROW(Sheet3!E2:M5,
                      LAMBDA(a,
                             COUNTIF(a,
                                     Formula!$A$2
                                     ) >= 1
                            )
                       )
                  ),
            "Search term not found"
       )

The BYROW/LAMBDA combination creates a one dimensional array (the same size as the list of items in Sheet3 column A.) of TRUE and FALSE values for any row where the search term is found at least once.

The FILTER function, finds all the rows that have a corresponding "TRUE" value and only displays those.

The IFERROR function (although not strictly part of the original request) explains what is happening if search term is not found (instead of displaying a #CALC error)

Note that the FILTER function must have sufficient rows beneath it to display all results (otherwise a #SPILL error will be shown).

Upvotes: 0

Related Questions