How to Return Multiple Lookup Matches Horizontally in Excel 2016
Question details
The user needs a way to look up a specific value and return all corresponding matches distributed horizontally across multiple columns in Excel 2016.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- A user has a dataset with multiple matching records for a single lookup value and wants to extract all matching results into adjacent columns in a single row.
- Observed behavior
- Excel 2016 does not support the newer dynamic-array FILTER function, requiring users to rely on complex legacy array formulas or helper columns to distribute multiple matches horizontally.
Since Excel 2016 lacks dynamic array support, you will be using legacy array formulas. Remember that these formulas require you to press Ctrl+Shift+Enter instead of just Enter to work correctly.
Use an INDEX and SMALL Array Formula
Extract multiple matches horizontally across columns using a combination of INDEX, SMALL, IF, and COLUMN functions.
This approach uses the IF function to identify the row numbers of all matching records. The SMALL function extracts these row numbers one by one from smallest to largest as you drag the formula across columns. Finally, the INDEX function retrieves the corresponding values from those rows.
Click on the cell where you want the first horizontal match to appear (e.g., C2).
Type the formula: =IFERROR(INDEX($B$2:$B$10, SMALL(IF($A$2:$A$10=$D2, ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1), COLUMN(A1))), "") where A2:A10 is the lookup range, B2:B10 is the return range, and D2 is the lookup value.
Instead of pressing Enter, press Ctrl+Shift+Enter. Excel will automatically wrap the formula in curly braces {}.
Click and hold the fill handle at the bottom right of the cell, then drag it across the adjacent columns to the right to display the second, third, and subsequent matches.
Use a Helper Column with VLOOKUP or INDEX/MATCH
Simplify the extraction process by creating a helper column that assigns a unique ID to each occurrence of the lookup value.
Master Advanced Data Extraction with WPS Spreadsheet
WPS Spreadsheet provides robust support for complex data lookups, array formulas, and advanced functions, making data extraction fast and efficient without performance lags.
- 1. Open your workbook: Launch WPS Spreadsheet and open your document containing the data.
- 2. Apply your formulas: Select your destination cell and type in your INDEX and SMALL array formula.
- 3. Execute and drag horizontally: Press Ctrl+Shift+Enter to confirm the array formula, then drag the fill handle horizontally to extract all matching values efficiently.
Frequently Asked Questions
Why does my array formula return a #NUM! error instead of empty cells?
The #NUM! error occurs when the SMALL function tries to find the next match but no further matches exist for that lookup value. To fix this, wrap your entire formula in the IFERROR function, like =IFERROR(your_formula, ""), which will display a blank cell instead of the error.
Can I use the FILTER function in Excel 2016?
No, the FILTER function is a dynamic array function that was introduced in Microsoft 365 and Excel 2021. Users on Excel 2016 or older versions must rely on legacy array formulas (like INDEX and SMALL) or helper column workarounds to achieve similar results.
Why is my INDEX/SMALL formula returning the exact same value in every column?
If you are getting identical values horizontally, ensure you are using the COLUMN function correctly at the end of your formula (e.g., COLUMN(A1)). Also, double-check that your lookup and return ranges are locked with absolute references (like $A$2:$A$10) so they don't shift when you drag the formula across columns.
