The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches.

Array formula in C8:

=VLOOKUP($B$8, INDEX(tbl, SMALL(IF($B$8=INDEX(tbl, , 1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROW(1:1)), , 1), 2, FALSE) + CTRL + SHIFT + ENTER copied down as far as needed.

Alternative array formula in C8:

=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed.

I recommend using the alternative array formula. Although VLOOKUP is not used in the formula, it is easier to understand and troubleshoot.

Explaining the alternative array formula

Step 1

=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1)))

In cells $B$2:$B$6 we want to identify cells equal to cell $B$8.

$B$8=$B$2:$B$6

becomes

"Pen"={"Pen", "Eraser", "Paper", "Pen", "Paper Clip"}

becomes

{"Pen"="Pen", "Pen"="Eraser", "Pen"="Paper", "Pen"="Pen", "Pen"="Paper Clip"}

$B$8=$B$2:$B$6 returns an array containing TRUE and/or FALSE.

In this example: (TRUE, FALSE, FALSE, TRUE, FALSE)

=INDEX($C$2:$C$6, SMALL(IF((TRUE, FALSE, FALSE, TRUE, FALSE), ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1)))

Step 2

=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1)))

This part of the formula creates an array containing numbers from one to as many rows cell range $B$2:$B$6 have.

$B$2 - Row number 1
$B$3 - Row number 2
$B$4 - Row number 3
$B$5 - Row number 4
$B$6 - Row number 5

ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1 returns this array: (1,2,3,4,5)

=INDEX($C$2:$C$6, SMALL(IF((TRUE, FALSE, FALSE, TRUE, FALSE),(1,2,3,4,5) , ""), ROW(A1)))

Step 3

=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1)))

Each cell in $B$2:$B$6 equal to $B$8 returns a row number.

IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, "") returns (1,"","",4,"")

=INDEX($C$2:$C$6, SMALL((1,"","",4,""), ROW(A1)))

Step 4

=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1)))

This part of the formula returns the k-th smallest number in the array (1,"","",4,"")

To calcualte the k-th smallest value I am using ROW(A1) to create the number 1.

When the formula in C8 is copied to cell C9, ROW(A1) changes to ROW(A2). ROW(A2) is 2.

In Cell C8: =INDEX($C$2:$C$6, SMALL((1,"","",4,""), ROW(A1)))

=INDEX($C$2:$C$6, SMALL((1,"","",4,""), 1))

The smallest number in array (1,"","",4,"") is 1.

In Cell C9: =INDEX($C$2:$C$6, SMALL((1,"","",4,""), ROW(A2)))

=INDEX($C$2:$C$6, SMALL((1,"","",4,""), 2))

The second smallest number in array (1,"","",4,"") is 4.

Step 5

=INDEX($C$2:$C$6,1) is $1,50

=INDEX($C$2:$C$6,4) is $1,70

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

Named ranges

tbl (B2:C6)
What is named ranges?

Download excel file for this tutorial.

Vlookup.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 row 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

Read more articles about this topic:

• Using array formula to look up multiple values in a list
• Search for multiple text strings in multiple cells in excel

Ream more Excel – Search/Lookup

Related posts:

1. Vlookup with 2 or more lookup criteria and return multiple matches in excel
2. Lookup a value in a list and return multiple matches in excel
3. Vlookup of three columns to pull a single record
4. Match two criteria and return multiple rows in excel
5. Return multiple values if above frequency criterion in excel
6. Return multiple values if in range in excel
7. Lookup values in a range using two or more criteria and return multiple matches in excel
8. Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
9. Sum adjacent values using multiple lookup text values in a column in excel
10. Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel