Return All Matches Lookup Formulas
Pull every row that matches, not just the first.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Write formulas that return all matching rows for a lookup value. App and version: [SPREADSHEET_APP_AND_VERSION]. Data layout: [DATA_LAYOUT]. Lookup cell: [LOOKUP_CELL]. Columns to return: [RETURN_COLUMNS]. Sample data (fake): [SAMPLE_DATA].
Provide solutions from newest to oldest approach:
1. FILTER-based: =FILTER(B2:D100, A2:A100=F1, "No matches") for returning several columns; sorting and limiting with SORT and TAKE (if available).
2. Join into one cell: =TEXTJOIN(", ", TRUE, FILTER(B2:B100, A2:A100=F1)).
3. Older versions: a helper column that numbers matches (=IF(A2=$F$1, COUNTIF($A$2:A2, $F$1), "")), then INDEX/MATCH on the number 1, 2, 3 to retrieve each match.
4. Array formula alternative with SMALL and ROW (explain how it works).
5. Counting matches first with COUNTIF so the output area can be sized.
For each, explain the step-by-step logic with the sample data, the spill range behavior, how to handle zero matches, case sensitivity, partial matches with wildcards, and performance on large ranges. Include a test with one, several, and no matches, and a chart of which approach to use for different versions. Warn that the output area must have free cells.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.