Ahmed Ali asks:

How to return multiple values using vlookup in excel and removing duplicates?
my sheet is setup as follows

A B C D E
1 Section Category item flavor size
2 food Coffee Espresso none Single
3 food Coffee Espresso none double
4 food Coffee Americano none Single
5 food Coffee Americano none double

i have tried the formula to return multiple values using the index example and worked fine with none duplicate item but how can i list them without the duplicate?

Answer:

This answer contains no Vlookup function.

Array formula in cell E8:

=INDEX(Item, SMALL(IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

Named ranges

Category C3:C6
Item D3:D6

What is named ranges?

Create named ranges

  1. Select cell range C3:C6
  2. Type Category in name box. See picture above.
  3. Repeat with remaining ranges

Copy array formula

  1. Select cell E8
  2. Copy cell ( Ctrl + c)
  3. Select cell E9
  4. Paste (Ctrl - v)

Remove errors (Excel 2007)

=IFERROR(INDEX(Item, SMALL(IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1)), "")

Explaining array formula in cell E8

Step 1 - Compare cell value in C8 with column Category and return a boolean array

=INDEX(Item, SMALL(IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

$C$8=Category

becomes

$C$8=$C$3:$C$6

becomes

"Coffee"={"Coffe"; "Coffe"; "Coffe"; "Coffe"}

and returns

{"TRUE"; "TRUE"; "TRUE"; "TRUE"}

Step 2 - Remove previous values (duplicates) from list and return a boolean array

=INDEX(Item, SMALL(IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

(COUNTIF($E$7:E7, Item)=0)

becomes

(COUNTIF($E$7:E7, $D$3:$D$6)=0)

becomes

(COUNTIF("", {"Espresso";"Espresso";"Americano";"Americano"})=0)

becomes

({0;0;0;0}=0)

and returns {TRUE;TRUE;TRUE;TRUE}.

Step 3 - Convert values in boolean array to corresponding row numbers

=INDEX(Item, SMALL(IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}*{"TRUE"; "TRUE"; "TRUE"; "TRUE"}, ROW(Category)-MIN(ROW(Category))+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, ROW(Category)-MIN(ROW(Category))+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, {3;4;5;6}-MIN(ROW(Category))+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, {3;4;5;6}-MIN({3;4;5;6})+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, {3;4;5;6}-3+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, {1;2;3;4}, "")

and returns {1;2;3;4}

Step 4 - Return the k-th smallest number

=INDEX(Item, SMALL(IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

SMALL(IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1)

becomes

SMALL({1;2;3;4}, 1)

becomes

SMALL({1;2;3;4}, 1)

and returns 1.

Step 5 - Return a value or reference of the cell at the intersection of a particular row and column

=INDEX(Item, SMALL(IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

becomes

=INDEX(Item, 1)

and returns Espresso in cell E8.

Download excel file

Vlookup - Return multiple unique distinct values.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order.

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest number in this data set.

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

Recommended blog articles