How to use a formula to return multiple matches in separate rows
Category: [Excel] Demo Available
How to use a formula to return multiple matches in separate rows
Requirement:
The user has one sheet with two tabs - Summary & Data. The user is trying to populate the Class column in the Summary tab with information on the Data tab using the account # column (file also attached). The problem the user is having is that some account has multiple "Classes" - examples 5254-03002-1 & 8284-00015-5. The user has tried the Vlookup formula but it only picks up the first match and ignores other matches. What would be the appropriate formula to use in this scenario?
< Summary table>
| Account# | Class |
| 5254-03002-1 | 1 |
| 5254-03002-1 | 9 |
| 8081-21003-9 | 1 |
| 8284-00015-5 | 6 |
| 8284-00015-5 | 8 |
| 8313-91002-1 | 6 |
| Account# | Class |
| 5254-03002-1 | 1 |
| 5254-03002-1 | 9 |
| 8081-21003-9 | 1 |
| 8284-00015-5 | 6 |
| 8284-00015-5 | 8 |
| 8313-91002-1 | 6 |
Solution:
The following Array* formula can provide the solution:
=INDEX(Data!$C$2:$C$7,SMALL(IF(Data!$B$2:$B$7=B2,ROW(Data!$B$2:$B$7)-ROW(Data!$B$2)+1),COUNTIF($B$2:$B2,$B2)))
Another way without array formulas is to use a helper column.
In the Data sheet you can add helper formula to D2, copied down:
=B2&"_"&COUNTIF(B$2:B2,B2)
then in Summary sheet, D2:
=INDEX(Data!$C$2:$C$7,MATCH(B2&"_"&COUNTIF(B$2:B2,B2),Data!$D$2:$D$7,0))
copied down
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to compare 2 date ranges when name matches |
| How to check values in a column and if condition matches then populate 3 other columns. |
| How to copy and paste column in wkbk 1 if its cell has text which matches with a cell of wbk 2 |
| How to compare 2 columns align matches (retaining formula) move columns 3 to 6 with column 2 |
| How to use a UserForm: CheckBox Checked if Listbox column 5 text matched CheckBox text |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.