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.

Named ranges

tbl (B2:C6)
What is named ranges?

How to implement array formula to your workbook

Change the named range. Change bolded values in the formula above.

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:

Ream more Excel – Search/Lookup

  • Share/Bookmark

Related posts:

  1. Vlookup with 2 or more lookup criteria and return multiple matches in excel
  2. Vlookup of three columns to pull a single record
  3. Sum adjacent values using multiple lookup text values in a column in excel
  4. Lookup a value in a list and return multiple matches in excel
  5. Lookup values in a range using two or more criteria and return multiple matches in excel
  6. Lookup with multiple criteria and display multiple search results using excel formula, part 2
  7. Lookup with multiple criteria and display multiple search results using excel formula, part 3
  8. Lookup with multiple criteria and display multiple search results using excel formula
  9. Search for multiple text strings in multiple cells in excel
  10. Return multiple values if in range in excel