Update: 30 Aug 2017

The VLOOKUP function is designed to return only a corresponding value of the first instance of a lookup value, from a column you choose. But there is a workaround to identify multiple matches.

The array formulas demonstrated below are smaller and easier to understand and troubleshoot than the useful VLOOKUP function.

You can also build an array formula that lets you look for a partial match or a wildcard search.

However you are not limited to array formulas, Excel also has built-in features that work very well, you will be amazed how easy it is to filter values in a data set.

Table of Contents

  1. Return multiple values vertically
    1. How to create an array formula
    2. Video
    3. Explaining formula (Return values vertically)
    4. Download workbook
    5. How to remove #num errors
    6. Count matching values
  2. Return multiple values horizontally
  3. Return multiple records
    1. Video
  4. Lookup and return multiple values [AutoFilter]
    1. Video
  5. Lookup and return multiple values [Advanced Filter]
  6. Lookup and return multiple values [Excel Defined Table]
  7. Return multiple values vertically or horizontally [UDF]
    1. Video
  8. Lookup and return multiple values in one cell
  9. Lookup across multiple sheets
  10. Vlookup – Return multiple unique distinct values in excel
  11. Search for a text string and return multiple adjacent values
  12. Lookup and return multiple values from a range excluding blanks

Return multiple values vertically

The image below shows you an array formula that extracts adjacent values based on a lookup value. The formula in cell G3 is not using the VLOOKUP function to extract values because that would make the formula bigger and harder to understand.

Another great thing with this array formula is that it allows you to search and return values from whatever columns you like contrary to the VLOOKUP function that lets you only do a lookup in the left-most column, in a range.

Array formula in G3:

=INDEX($B$3:$C$7, SMALL(IF(($E$3=$B$3:$B$7), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, ""),ROWS($A$1:A1)), 2)

This Youtube video explains how to VLOOKUP and return multiple matching values:

The array formula in cell G3 looks in column B for "France" and return adjacent values from column C. The array formula in cell G3 filters values unsorted, if you want to sort returning values alphabetically, read this:

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

Comments(2) Filed in category: Excel, VLOOKUP and return multiple values

How to create an array formula

You don't need to follow these steps if you chose the regular formula above.

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

Back to top

The array formula above filters only values with one condition, the following article explains how to filter based on multiple criteria:

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Comments(38) Filed in category: Excel, VLOOKUP and return multiple values

If you don't like array formulas, try this regular but more complicated formula in cell G3:

=INDEX($C$3:$C$7,SMALL(INDEX(($E$3=$B$3:$B$7)*(MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)))+($E$3<>$B$3:$B$7)*1048577,),ROWS($A$1:A1)))

Back to top

If you don't want to return matching values from a single column you can use the formula in this post to extract values from multiple columns:

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.

Comments(0) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Perhaps you want to filter a list based on a case sensitive search, navigate to this post:

Search case sensitive and return multiple values

Array formula in D4: =INDEX($A$1:$A$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell D4 and […]

Comments(0) Filed in category: Case sensitive, Excel, VLOOKUP and return multiple values

The SEARCH function allows you to search cell values that contain the lookup value, in other words, a wildcard lookup:

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

The following article demonstrates how to search for a specific consecutive sequence:

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]

Comments(1) Filed in category: Excel, Sequence

You can also return unique distinct values with a condition with a formula. Recommended article:

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

If you want the unique distinct list to be sorted alphabetically, read this article:

Unique distinct list sorted alphabetically and based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

Explaining array formula (Return values vertically)

You can easliy follow along as I explain the formula, select cell F3. Go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.

Click "Evaluate" button shown above to move to next step.

Step 1 - Identify cells equal to the criterion

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

$E$3=$B$3:$B$7

becomes

"France"={"France";"Germany";"Italy";"France";"Italy"}

and returns

{TRUE, FALSE, FALSE, TRUE, FALSE}

Step 2 - Create array containing corresponding row numbers

ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1

becomes

{2,3,4,5,6} - MIN({2,3,4,5,6})+1

becomes

{2,3,4,5,6} - 2+1

becomes

{2,3,4,5,6} - 1

becomes

{1,2,3,4,5}

ROW function explained

ROW(reference) Returns the row number of a reference. You can enter a reference to a single cell or a cell range. […]

Comments(1) Filed in category: Excel, Row

Step 3 - Filter row numbers equal to criterion

IF(($E$3=$B$3:$B$7),ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1,"")

becomes

IF({TRUE, FALSE, FALSE, TRUE, FALSE},{1,2,3,4,5},"")

and returns {1,"","",4,""}

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

Step 4 - Return the k-th smallest row number

SMALL(IF(($E$3=$B$3:$B$7),ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1,""),ROWS($A$1:A1))

becomes

SMALL({1,"","",4,""}, ROWS($A$1: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 ROWS($A$1:A1) to create the number 1.

When the formula in cell C8 is copied to cell C9, ROWS($A$1:A1) changes to ROWS($A$1:A2). ROWS($A$1:A2) returns 2.

In Cell C8: =INDEX($C$3:$C$7, SMALL((1,"","",4,""), ROWS($A$1:A1))

=INDEX($C$3:$C$7, SMALL((1,"","",4,""), 1))

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

In Cell C9: =INDEX($C$3:$C$7, SMALL((1,"","",4,""), ROWS($A$1:A2)))

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

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

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

Step 4 - Return value based on relative row number

In Cell C8:

=INDEX($C$3:$C$7,1)

becomes

=INDEX({"Apple";"Pear";"Banana";"Lemon";"Orange"}, 1)

and returns "Apple" in cell C8.

In Cell C9:

=INDEX($C$3:$C$7,4) returns "Lemon"

Recommended article:

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Back to top

Download Excel *.xlsx file

How to return multiple values vertically.xlsx

How to remove #num errors

The following picture shows you the array formula extracting adjacent values based on a lookup value. The array formula is copied down to cell G6 however there are only two values shown, the remaining cells show nothing.

Array formula in cell G3:

=IFERROR(INDEX($C$3:$C$7, SMALL(IF(($E$3=$B$3:$B$7), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, ""),ROWS($A$1:A1))), "")

The IFERROR function lets you convert error values to blank cells or really in whatever value you want. In this case it returns blank cells.

Learn more about the IFERROR function:

IFERROR function

The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR […]

Comments(0) Filed in category: Excel

How to enter an array formula

Back to top

Count matching values

The following image shows you a data set in column B and C. The lookup value in cell E3 is used for identifying matching cell values in column B.

Formula in cell G3:

=SUMPRODUCT(($B$3:$B$7=E3)*1)

Learn more about the SUMPRODUCT function

How to use Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

Comments(2) Filed in category: Excel, SUMPRODUCT function

The MMULT function is like the SUMPRODUCT function but on stereoids, learn more here:

MMULT function – Matrix multiplication

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]

Comments(4) Filed in category: Excel, MMULT function

or check out the MMULT archive.

Back to top

Return multiple values horizontally

This array formula is entered in cell C9. Then copy cell C9 and paste to the right.

Array formula in C9:

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

Enter the formula as an array formula or use this regular but more complicated formula:

=INDEX($C$2:$C$5, SMALL(INDEX(($B$9=$B$2:$B$6)*(MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)))+($B$9<>$B$2:$B$6)*1048577, 0, 0), COLUMN(A1)))

Back to top

Download *.xlsx file

Return-multiple-values-horizontally.xlsx

Return multiple records

vlookup - return multiple records

Array formula in cell A10:

=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))

Copy cell A10 and paste to cell range B10:C10. Then copy A10:C10 and paste to cell range A11:C12.

Watch a video where I explain how to use the array formula and how it works

The formula above filters all matching values, if you want to return unique distinct records, read this article:

Filter unique distinct row records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

Comments(2) Filed in category: Excel, Unique distinct records

If you are looking for duplicate records, navigate to:

Extract duplicate records

This article describes how to filter duplicate rows. The array formula in this article contains countifs, a function introduced in […]

Comments(0) Filed in category: Duplicate records, Excel

The following article explains how to search for a text string in all data columns and return multiple matching records:

Search for a text string in a data set and return matching records

Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]

Comments(28) Filed in category: Excel, Filter records

These posts show you how to extract records using two conditions:

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

Comments(35) Filed in category: Excel, Filter records

Filter records between two dates

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

Comments(6) Filed in category: Excel, Filter records

Enter the formula above as an array formula or use this regular but more complicated formula:

=INDEX($A$2:$C$7, SMALL(INDEX(($B$9=$A$2:$A$7)*(MATCH(ROW($A$2:$A$7), ROW($A$2:$A$7)))+($B$9<>$A$2:$A$7)*1048577, 0, 0),ROW(A1)),COLUMN(A1))

How to create an array formula

Back to top

Download *.xlsx file

Vlookup-return-multiple-records.xlsx

Lookup and return multiple values [AutoFilter]

The AutoFilter is a built-in feature in Excel that allows you to quickly filter data. The following video shows you how to quickly filter a data set, I don't think you can do it more quickly than this.

Instructions on how to filter a data set [AutoFilter]

  1. Right-click on a cell value that you want to filter
  2. Click on "Filter" and then "Filter by Selected Cell's Value"
  3. That's it!

How to remove a filter

  1. Click on filter button next to header, shown in picture below
  2. Click on "Clear Filter From "Country""
  3. The AutoFilter buttons next to each header are still there.
  4. If you want to remove those as well, go to tab "Home" on the ribbon and click on "Sort & Filter" button, then on "Filter"
  5. The data set now looks like this:

 

Back to top

Return multiple values vertically or horizontally [UDF]

Make sure you have copied the vba code below into a standard module before entering the array formula.

=vbaVlookup(lookup_value, table_array, col_index_num, [h])

h is optional, h= return values horizontally

Array formula in cell C9:C11:

=vbaVlookup(B9, B2:C6, 2)

Watch a video that explains how to use the User Defined Function

How to enter custom function array formula

  1. Select cell range C9:C11
  2. Type above custom function
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

Array formula in cell C14:D14:

=vbaVlookup(B14, $B$2:$C$6, 2, "h")

Back to top

How to enter custom function array formula

  1. Select cell range C14:D14
  2. Type above custom function
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys

How to copy array formula to the next row

  1. Select cell range C14:D14
  2. Copy cell range
  3. Select cell range C15:D15
  4. Paste

Vba code

  1. Copy vba code below
  2. Press Alt + F11
  3. Insert a module in project explorer
  4. Paste into code window
  5. Return to Microsoft Excel
Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v")
Dim r As Single, Lrow, Lcol As Single, temp() As Variant

ReDim temp(0)

For r = 1 To tbl.Rows.Count
If lookup_value = tbl.Cells(r, 1) Then
temp(UBound(temp)) = tbl.Cells(r, col_index_num)
ReDim Preserve temp(UBound(temp) + 1)
End If
Next r

If layout = "h" Then
Lcol = Range(Application.Caller.Address).Columns.Count
For r = UBound(temp) To Lcol
temp(UBound(temp)) = ""
ReDim Preserve temp(UBound(temp) + 1)
Next r
ReDim Preserve temp(UBound(temp) - 1)
vbaVlookup = temp
Else
Lrow = Range(Application.Caller.Address).Rows.Count
For r = UBound(temp) To Lrow
temp(UBound(temp)) = ""
ReDim Preserve temp(UBound(temp) + 1)
Next r
ReDim Preserve temp(UBound(temp) - 1)
vbaVlookup = Application.Transpose(temp)
End If

End Function

Back to top

Download *.xlsm file

Vlookup-vba.xlsm

Related articles

This post explains how to do misspelled lookups or filter values very similar to the lookup value:

Excel udf: Fuzzy lookups

In this post I will describe a basic user defined function with better search functionality than the array formula in […]

Comments(2) Filed in category: Excel, Vlookup

Lookup with multiple criteria and display multiple search results using excel formula

Question: How do I search a list containing First name column and a last name column? I want to search […]

Comments(47) Filed in category: Excel, Search and return multiple values

Lookup with multiple criteria and display multiple unique search results (array formula)

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Comments(5) Filed in category: Excel, MMULT function, Search and return multiple values, Unique distinct values

Lookup with multiple criteria and display multiple search results using excel formula, part 2

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Comments(3) Filed in category: Excel

Lookup with multiple criteria and display multiple search results using excel formula, part 3

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Comments(16) Filed in category: Excel

Lookup with multiple criteria and display multiple search results using excel formula, part 4

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Comments(27) Filed in category: Excel, Search and return multiple values

Nested Search

Minh Hung asks: Hello Mr Oscar I have the matter to create a megaformula to categorize my list. For short […]

Comments(1) Filed in category: Excel, Search and return multiple values

Back to top