Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no luck.
How do you find multiple occurances? Do I need to use match function?

Answer:

Array formula in B25:

=INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1)))

How to create an array formula

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

Copy cell B25 and paste it down as far as needed.

Remove #NUM errors

Excel 2007:

=IFERROR(INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1))), "")

Excel 2003:

=IF(COUNTIF($B$3:$B$16, $B$21)>=ROWS($A$1:A1), INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1))), "")

Explaining array formula in cell B25

=INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1)))

Step 1 - Find matching cells using a comparison operator

=INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1)))

= (equal sign) is a comparison operator and checks if criterion ($B$21) is equal to values in array ($B$3:$B$16). This operator is not case sensitive.

$B$21=$B$3:$B$16

becomes

Maine = {"Kansas"; "Maine"; "South Dakota"; "Montana"; "Delaware"; "Kentucky"; "South Carolina"; "South Dakota"; "Oregon"; "Maine"; "Oregon"; "Delaware"; "Maine"; "Kansas"}

and returns

{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}

Step 2 - Convert boolean values to row numbers

=INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1)))

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

IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, "")

becomes

IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, "")

becomes

IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}-MIN({3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16})+1, "")

becomes

IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}-3+1, "")

becomes

IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}-2, "")

becomes

IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}, "")

and returns

{""; 2; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; 13; ""}

Step 3 - Return the k-th smallest number

=INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1)))

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

SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1))

becomes

SMALL({""; 2; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; 13; ""}, ROW(A1))

becomes

SMALL({""; 2; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; 13; ""}, 1)

returns 2.

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

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.

=INDEX($C$3:$C$16, SMALL(IF($B$21=$B$3:$B$16, ROW($B$3:$B$16)-MIN(ROW($B$3:$B$16))+1, ""), ROW(A1)))

becomes

=INDEX($C$3:$C$16, 2)

becomes

=INDEX({"Smith"; "Johnson"; "Williams"; "Jones"; "Brown"; "Davis"; "Miller"; "Wilson"; "Moore"; "Taylor"; "Anderson"; "Thomas"; "Lopez"; "Larsen"}, 2)

and returns Johnson in cell B25.

Download excel sample file for this tutorial.

Using array formula to look up multiple values in a list.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article

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.

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.

ROW(reference)
Returns the row number of a reference

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

Recommended articles

Check out these posts and learn more about vlookup.