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?

### 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.

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

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