vlookup fails when you need to lookup values from a list where multiple different values can be reported.
This is a method to obtain all possible values for a specific value.
Here is an example list. For the example, placing this from cell A1.
| Item | Value |
|---|---|
| a | 1 |
| a | 2 |
| b | 3 |
| c | 4 |
| c | 5 |
| a | 6 |
| d | 7 |
As can be seen some items have multiple values.
For example, 'a' has values of '1', '2' and '6'.
A vlookup(list, 'a', 2, false) query to find the value of 'a' would return '1' but not the other values.
Setup in a different area a list of all unique items from the list. This needs to de-dup all possible values from 1 first.
As can be seen from the main list, the only unique items are: a, b, c and d.
For the example, placing this from cell A14.
| Item |
|---|
| a |
| b |
| c |
| d |
Add the following formula to the right of the unique items.
Don't just add it into one cell, but assuming that there can be 10 different values for an item, then add this to the next 10 cells to the right as well.
=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A14=$A$2:$A$8,ROW($A$2:$A$8)- MIN(ROW($A$2:$A$8))+1,""), COLUMN()-1)),"")
IMPORTANT: This formula needs to be entered as an ARRAY formula and not just a regular formula.
Therefore when you have that formula entered into the Cell, do not just press <ENTER> to accept it, but instead press <CTRL><SHIFT><ENTER>.
To check that it has been entered as an ARRAY formula, you should notice that the formula will be with squiggly brackets { }.
NOTE: The formula references some existing CELLS. Adjust this as needed.
These are:
| a | 1 | 2 | 6 |
| b | 3 | ||
| c | 4 | 5 | |
| d | 7 |
Result show all possible Values against each Item.
Setup a 3rd results area which will contain the final output.
For the example, placing this from cell A23.
On the 1st row of this Result area place the following:
| Item | Value |
|---|---|
| =$A$14 | =IFERROR(OFFSET($A$14,MATCH(A23,$A$14:$A$17,0)-1,COUNTIF($A$23:A23,A23)),“”) |
NOTE: An alternative formula is to use the following ARRAY formula for that formula on the right. Remember to press <CTRL><SHIFT><ENTER> on each of the cells to ensure it is saved as an ARRAY formula.
| =IFERROR(INDEX($A$14:$E$17,MATCH(A23,$A$14:$A$17),COUNTIF($A$22:$A23,A23)+1),“”) |
On the 2nd row of this Result area place the following:
Place the following formula in the next row down, under the *|=$A$14|*
| =IFERROR(IF(OFFSET($A$14,MATCH(A23,$A$14:$A$17,0)-1,COUNTIF($A$23:A23,A23)+1)=“”,INDEX($A$14:$A$17,MATCH(A23,$A$14:$A$17,0)+1),A23),“”) |
and copy the formula from the 1st Row's right column alongside this.
NOTE: The formula references some existing CELLS. Adjust this as needed.
These are:
On 3rd row onward of the Result area:
Copy the formulas from the 2nd row down for as many rows as needed to cover different unique Item/Value combinations that may exist from the original list.
Don't worry is this is copied down for more cells than needed. Once all possible Item/Values combinations are displayed the rows below will show empty values.
This should produce a result where all unique Item/Value combinations are displayed and which can be copied & pasted-as-values elsewhere.
| Item | Value |
|---|---|
| a | 1 |
| a | 2 |
| a | 6 |
| b | 3 |
| c | 4 |
| c | 5 |
| d | 7 |
Note that last few are blank for both Item and Value. This is fine. Even if this went down for many more rows that were blank this would be fine too.
The items we are interested in includes everything up to the first fully blank line.
De-dup if necessary.