Table of Contents
Microsoft Excel - Lookups - Lookup unique values from a list where vlookup will not work
Unique Identifier Sheet
Instructions of how to use this to identify unique items from a list.
This can be used where vlookup will not work. For example where a lookup can return more than one value, vlookup only returns a single value.
This sheet provides a means whereby all unique values can be ascertained from a list, mostly automated.
NOTE: The assumption here is that there can be a maximum of 10 possible values per Item in the original list.
This is covered by columns E through N.
Extended and adjust other formulas if needed to cater for more than 10 values per item.
Step Instructions 1 Populated columns A and B with the list of items directly from the original source. 2 Copy only the lookup values (column A) to column D. 3 Sort column D in ascending order. 4 Remove all duplicate values from column D. 5 Copy the formula in E2 through N2 down against each remaining value in column D. 6 Copy the formula in P3 and Q3 down until column P returns no further values. 7 Copy the P and Q columns to columns S and T as values. 8 Remove all duplicate values from columns S and T. 9 Done. Columns S and T now contain all unique values from the original list. Notes: 1 The process supports up to 10 values per lookup value. These are handled by columns E through N. If more values are needed then simply add additional columns after column N, and copy the formula across too. The formulas are written in such a way that they will auto-detect and pick up these additional columns so no need to modify them. 2 DO NOT ATTEMPT TO CHANGE THE FORMULAS UNLESS YOU KNOW AND UNDERSTAND "ARRAY" FORMULAS.
Top Row
Top row contains the headings.
Populate the top row as:
| ID | Value | ID | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | ID | Value | ID | Value |
Populate the sheet with the original source Items and Values
Populate columns A and B with the list of items directly from the original source that will be looked up.
Populate the actual Items that will be looked up
Copy only the lookup values (column A) to column D.
Sort column D in ascending order.
Remove all duplicate values from column D.
Populate the lookup formula
Populate the following formula in E2 through N2.
Also Populate this same formula down against every other row that contains a value in column D.
=IFERROR(T(INDEX($B$2:$B$16775,SMALL(IF($D2=$A$2:$A$16775,ROW($A$2:$A$16775)- MIN(ROW($A$2:$A$16775))+1,""), COLUMN()-1-3))),"")
IMPORTANT: This formula needs to be entered an an Array Formula.
Therefore once the actual formula is entered into the cell, complete it using <CTRL><SHIFT><ENTER>.
Confirm that it is an Array Formula. This will start and end with squiggly brackets { }.
NOTE: The assumption here is that there can be a maximum of 10 possible values per Item in the original list.
This is covered by columns E through N.
Extended and adjust other formulas if needed to cater for more than 10 values per item.
Populate the top unique Item values formula
Populate the following formula in cell P2.
=D2
and populate the following into Q2.
=IFERROR(OFFSET($D$2,MATCH(P2,$D$2:$D$8000,0)-1,COUNTIF($P$2:P2,P2)+2),"")
Populate the rest of the unique Item values formulas
Populate the following formula into P3.
=IFERROR(IF(OFFSET($D$2,MATCH(P2,$D$2:$D$8000,0)-1,COUNTIF($P$2:P2,P2)+1)="",INDEX($D$2:$D$8000,MATCH(P2,$D$2:$D$8000,0)+1),P2),"")
and copy the formula that is in Q2 to Q3.
Now copy the formula in P3 and Q3 down until column P returns no further values.
Set unique Items and Values to values only
Copy the P and Q columns to columns S and T as values; i.e. to remove the formulas.
Remove all duplicate values from columns S and T.
Done.
Columns S and T now contain all unique values from the original list.