Author: Oscar Cronquist Article last updated on September 05, 2018

Picture of how to extract multiple values based on a condition using an array formula

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.

Picture of how to extract multiple values based on a lookup value using an array formula

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:


Subscribe to Get Digital Help on Youtube:

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 […]

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.
    Picture showing 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.

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.

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

Case sensitive lookup and return multiple values

The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]

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 […]

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 […]

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 […]

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

Unique distinct list sorted alphabetically based on a condition

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

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.

Picture showing Evaluate Formula dialog box

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}

How to use the ROW function

The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]

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

How to use the IF function

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

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.

How to use the SMALL function

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

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:

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

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.

Picture of how to extract multiple values based on a condition using an array formula and errors are hidden

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:

How to use the IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]

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.

Picture of how to count multiple values based on a condition using a formula

Formula in cell G3:

=COUNTIF($B$3:$B$7,E3)

Alternative formula in cell G3:

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

Learn more about the SUMPRODUCT function

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

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

How to use the MMULT function

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and […]

or check out the MMULT archive.

Back to top

Return multiple values horizontally

Picture of how to extract multiple values horizontally based on a condition using an array formula

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

Picture of how to extract multiple records based on a condition using an array formula

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

Subscribe to Get Digital Help on Youtube:

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

Filter unique distinct records

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

If you are looking for duplicate records, navigate to:

Extract duplicate records

This article describes how to filter duplicate rows with the use of a formula.  It is, in fact, an array […]

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 […]

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.

Extract records between two dates

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

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.

Subscribe to Get Digital Help on Youtube:

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"
    Picture of how to filter multiple values based on a condition using AutoFilter
  3. That's it!

How to remove a filter

  1. Click on filter button next to header, shown in picture below
    Picture of how to filter values based on a condition using AutoFilter
  2. Click on "Clear Filter From "Country""
    icture of how to clear filter using AutoFilter
  3. The AutoFilter buttons next to each header are still there.
    Picture of AutoFilter buttons
  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"
    Picture of how to remove AutoFilter buttons
  5. The data set now looks like this:

 

Back to top

Return multiple values vertically or horizontally [UDF]

Picture of an User defined function that extracts values based on a condition

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

Subscribe to Get Digital Help on Youtube:

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

How to enter an array formula

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

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
    Picture of VB Explorer window
  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:

Fuzzy lookups [UDF]

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

Lookup with multiple criteria and return multiple search results

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

Search each column for a string each and return multiple records – OR logic

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

A record is returned if both search strings are found on the same row

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

Search each column for a string each and return multiple records – AND logic

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

Extract records where all criteria match if not empty

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

Nested Search

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

Back to top