Multiple Matches into Separate Columns? Excel Fix

Yes, you can split multiple lookup matches into separate columns in Excel. The standard VLOOKUP or INDEX/MATCH returns only the first match, but by combining INDEX, SMALL, IF, and ROW functions (entered as an array formula), you can extract every match and display each one in its own column. This article explains the formula, how it works, and how to adapt it to your data.

Formula

To pull multiple matches into separate columns, use this array formula in the first cell of your output row (for example, cell C2):

=IFERROR(INDEX($B$2:$B$100, SMALL(IF($A$2:$A$100=$E$1, ROW($B$2:$B$100)-ROW($B$2)+1), COLUMN()-COLUMN($C$2)+1)), "")

Enter it with Ctrl+Shift+Enter (in older Excel) or just Enter if you have Dynamic Arrays (Excel 365). Then drag the formula to the right to fill as many columns as you need. The formula returns the first match in the first column, the second match in the second column, and so on.

Variables:

• $A$2:$A$100 – the range containing the lookup values (the column you search in)
• $B$2:$B$100 – the range containing the values to return (the column you pull from)
• $E$1 – the cell with the value you are matching
• $C$2 – the first cell where you enter the formula

How it works: The IF function creates an array of row numbers where the lookup value matches. SMALL extracts the k-th smallest row number (k = 1 for the first match, 2 for the second, etc.). INDEX then returns the value from that row. COLUMN()-COLUMN($C$2)+1 generates the k value as you drag right.

Worked Example

Suppose you have:

• A2:A6 = {“Apple”, “Banana”, “Apple”, “Cherry”, “Apple”}
• B2:B6 = {10, 20, 30, 40, 50}
• E1 = “Apple”

You want to list all prices for Apple in separate columns. In C2, enter the formula above. Since it’s an array formula, press Ctrl+Shift+Enter. Drag C2 to the right to D2, E2, etc.

Result: C2 = 10, D2 = 30, E2 = 50. The formula finds row 2 (first Apple), then row 4 (second Apple), then row 6 (third Apple). When no more matches exist, IFERROR returns an empty string.

If you only need 3 matches, you can wrap the formula with =IF(COLUMN()-COLUMN($C$2)+1 > 3, "", ...) to limit the number of columns.

When to Use This Instead of VLOOKUP

VLOOKUP and INDEX/MATCH are designed to return a single value. They stop at the first match. If your data has duplicate lookup values and you need all related records, this array formula is the standard solution. Use it when:

• You have duplicate IDs, names, or categories and need every associated record.
• You want the results side-by-side in columns (not stacked in rows).
• You need a dynamic solution that updates when data changes.

For a simpler but less flexible approach, you can use a PivotTable or Power Query to transform the data, but those require more setup and are not as direct for a one-off extraction.

How to Set Up the Formula Correctly

1. Identify your lookup range (A column) and return range (B column).
2. Pick a cell for the lookup value (e.g., E1).
3. Select the first cell where you want the first match to appear (e.g., C2).
4. Enter the formula, but do not press Enter yet.
5. Press Ctrl+Shift+Enter (for legacy Excel) or just Enter (for Excel 365 with dynamic arrays).
6. Drag the fill handle to the right to fill as many columns as you expect matches.

If you are using Excel 365, you can also use the FILTER function: =TRANSPOSE(FILTER($B$2:$B$100, $A$2:$A$100=$E$1)) which spills the results horizontally automatically. This is much simpler, but only works in Excel 365 and 2021.

Common Mistakes and How to Avoid Them

• Forgetting to press Ctrl+Shift+Enter: In older Excel, if you just press Enter, the formula returns #VALUE! or only the first match. Always use the array entry.
• Using absolute vs. relative references incorrectly: The lookup and return ranges must be absolute (with $). The column reference for the output cell must be relative (no $) so it changes as you drag right.
• Not accounting for no matches: Without IFERROR, the formula returns #NUM! when there are fewer matches than columns. Always wrap with IFERROR.
• Mixing row and column orientation: If your data is in rows instead of columns, you need to adjust the ROW function to COLUMN and vice versa. The formula above assumes vertical data.

Alternatives: FILTER, Power Query, and PivotTables

If you have Excel 365, the FILTER function is the easiest way to get multiple matches. For example, =TRANSPOSE(FILTER(B2:B100, A2:A100=E1)) returns all matches in a horizontal row. If you want them in separate columns, TRANSPOSE is exactly what you need.

Power Query (Get & Transform) can also split matches by grouping and transposing, but it requires refreshing the query when data changes. PivotTables can display multiple values, but they aggregate by default; you would need to set the value field to “Show Values As” and use the “Tabular” layout to see duplicates.

The array formula method works in all Excel versions and is the most portable. It is also the only method that gives you full control over the number of columns.

Frequently Asked Questions

Why does my formula return #VALUE! instead of the match?

You probably didn’t enter it as an array formula. Select the cell, press F2, then press Ctrl+Shift+Enter. In Excel 365, you can just press Enter, but older versions require the array entry.

How do I limit the number of columns to, say, 5?

Wrap the formula with an IF that checks the column offset. For example: =IF(COLUMN()-COLUMN($C$2)+1 > 5, "", IFERROR(...)). This leaves blank cells after the 5th match.

Can I use this with text values instead of numbers?

Yes, the formula works with any data type. Just make sure the return range (B column) contains the text you want to pull.

What if my data is in rows instead of columns?

You need to swap ROW for COLUMN and adjust the SMALL function accordingly. For horizontal data, use =IFERROR(INDEX($B$2:$Z$2, SMALL(IF($A$2:$A$100=$E$1, COLUMN($B$2:$Z$2)-COLUMN($B$2)+1), COLUMN()-COLUMN($C$2)+1)), "") and drag down instead of right.

Is there a non-array way to do this?

In Excel 365, FILTER is non-array and simpler. In older versions, you can use a helper column with COUNTIF to assign a match number, then use INDEX/MATCH. But the array formula is the most direct.

Why does dragging the formula give me the same value in every column?

You probably used absolute references for the output cell (e.g., $C$2) in the COLUMN part. Remove the $ so it becomes relative (C2) and then drag right.

Related

• Hide Your IP Address: Quick & Easy Ways
• Simpsons on Disney Plus Hotstar? Availability
• AI Automation: What It Can and Cannot Do Reliably

in n | Number of matches to show | - | 3 | 1 | 10
in r | Row number of first match (1-based) | - | 2 | 1 | 1000
out result | Match number | - | r + n - 1 | 0