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 occurrences? Do I need to use match function?

Answer:

Table of Contents

  1. How to enter an array formula
  2. Explaining array formula
  3. Remove errors
  4. Remove blanks
  5. Download excel file
  6. Functions in this post
  7. Recommended reading

The picture below shows states in column B and Names in column C. The search value is in B21 and the results are in B25 and cells below.

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)))

This post is very similar to this post: How to return multiple values using vlookup

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.

Back to top

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.

Back to top

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))), "")

Back to top

Remove possible blanks

The following picture shows a blank cell in C12.

This array formula in cell E7 takes care of blank cells:

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

Back to top

Download excel sample file for this tutorial.

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

Back to top

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

Back to top

Check out these posts and learn more about vlookup.

Back to top