-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Return Multiple Results of Vlookup Value
- Thread starter jopstam
- Start date
mgirvin
Well-known Member
- Joined
- Dec 15, 2005
- Messages
- 1,236
- Office Version
- 365
- Platform
- Windows
Here is the set up (formulas in green cells):
Enter this formula in cell E2:
=COUNTIF(A1:A4,D2)
Enter this formula in cell E5 with Ctrl + Shift + Enter (not just Enter) and copy down:
=IF(ROWS(E$5:E5)>$E$2,"",INDEX($B$1:$B$4,SMALL(IF($A$1:$A$4=$D$2,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS(E$5:E5))))
Or to get a horizontal orientation:
Enter this formula in cell E2:
=COUNTIF(A1:A4,D2)
Enter this formula in cell B6 with Ctrl + Shift + Enter (not just Enter) and to the side:
=IF(COLUMNS($I13:I13)>$E$2,"",INDEX($B$1:$B$4,SMALL(IF($A$1:$A$4=$D$2,ROW($B$1:$B$4)-ROW($B$1)+1),COLUMNS($I13:I13))))
| Excel Workbook | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | AAA | R1 | Item | Count | |||
| 2 | BBB | R2 | AAA | 3 | |||
| 3 | AAA | R3 | |||||
| 4 | AAA | R4 | Return items | ||||
| 5 | R1 | ||||||
| 6 | R3 | ||||||
| 7 | R4 | ||||||
| 8 | . | ||||||
| 9 | . | ||||||
| 10 | . | ||||||
| 11 | . | ||||||
... | |||||||
Enter this formula in cell E2:
=COUNTIF(A1:A4,D2)
Enter this formula in cell E5 with Ctrl + Shift + Enter (not just Enter) and copy down:
=IF(ROWS(E$5:E5)>$E$2,"",INDEX($B$1:$B$4,SMALL(IF($A$1:$A$4=$D$2,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS(E$5:E5))))
Or to get a horizontal orientation:
| Excel Workbook | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | AAA | R1 | Item | Count | |||||
| 2 | BBB | R2 | AAA | 3 | |||||
| 3 | AAA | R3 | |||||||
| 4 | AAA | R4 | |||||||
| 5 | |||||||||
| 6 | Returned Items | R1 | R3 | R4 | . | . | . | ||
... | |||||||||
Enter this formula in cell E2:
=COUNTIF(A1:A4,D2)
Enter this formula in cell B6 with Ctrl + Shift + Enter (not just Enter) and to the side:
=IF(COLUMNS($I13:I13)>$E$2,"",INDEX($B$1:$B$4,SMALL(IF($A$1:$A$4=$D$2,ROW($B$1:$B$4)-ROW($B$1)+1),COLUMNS($I13:I13))))
Last edited:
Upvote
0
mgirvin
Well-known Member
- Joined
- Dec 15, 2005
- Messages
- 1,236
- Office Version
- 365
- Platform
- Windows
If you have Excel 2010, this non-array formula (Does Not Need Ctrl + Shift + Enter) may work for you:
In cell E2:
=COUNTIF(A1:A4,D2)
In cell B6, then copy to the side:
=IF(COLUMNS($E9:E9)>$E$2,"",INDEX($B$1:$B$4,AGGREGATE(15,6,(ROW($B$1:$B$4)-ROW($B$1)+1)/($A$1:$A$4=$D$2),COLUMNS($E9:E9))))
In E9, then copy down:
=IF(ROWS(E$9:E9)>$E$2,"",INDEX($B$1:$B$4,AGGREGATE(15,6,(ROW($B$1:$B$4)-ROW($B$1)+1)/($A$1:$A$4=$D$2),ROWS(E$9:E9))))
| Excel Workbook | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | AAA | R1 | Item | Count | |||||
| 2 | BBB | R2 | AAA | 3 | |||||
| 3 | AAA | R3 | |||||||
| 4 | AAA | R4 | |||||||
| 5 | |||||||||
| 6 | Returned Items | R1 | R3 | R4 | . | . | . | ||
| 7 | |||||||||
| 8 | Return items | ||||||||
| 9 | R1 | ||||||||
| 10 | R3 | ||||||||
| 11 | R4 | ||||||||
| 12 | . | ||||||||
| 13 | . | ||||||||
| 14 | . | ||||||||
| 15 | . | ||||||||
... | |||||||||
In cell E2:
=COUNTIF(A1:A4,D2)
In cell B6, then copy to the side:
=IF(COLUMNS($E9:E9)>$E$2,"",INDEX($B$1:$B$4,AGGREGATE(15,6,(ROW($B$1:$B$4)-ROW($B$1)+1)/($A$1:$A$4=$D$2),COLUMNS($E9:E9))))
In E9, then copy down:
=IF(ROWS(E$9:E9)>$E$2,"",INDEX($B$1:$B$4,AGGREGATE(15,6,(ROW($B$1:$B$4)-ROW($B$1)+1)/($A$1:$A$4=$D$2),ROWS(E$9:E9))))
Upvote
0
Similar threads
- Question
- Question
