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. Although VLOOKUP is not used in these array formulas, they are easier to understand and troubleshoot.

Table of Contents

  1. Return multiple values vertically
    1. Explaining formula (Return values vertically)
  2. Return multiple values horizontally
  3. Return multiple records
  4. How to remove #num errors
  5. Return multiple values vertically or horizontally (vba)
  6. How to create an array formula
  7. Lookup across multiple sheets
  8. Vlookup – Return multiple unique distinct values in excel
  9. Search for a text string and return multiple adjacent values
  10. Lookup and return multiple values from a range excluding blanks

Return multiple values vertically

Array formula in F3:

=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)))

Enter the formula as an array formula

The array formula above filters values unsorted, if you want to sort returning values alphabetically, read this: Use VLOOKUP and return multiple values sorted from A to Z

You can also return unique distinct values with a condition with a formula. Recommended article: Vlookup – Return multiple unique distinct 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

Perhaps you want to filter a list based on a case sensitive search, navigate to this post: Search case sensitive and return multiple values in excel

The array formula above filters only values with one condition, the following article explains how to filter with many criteria: Vlookup with 2 or more lookup criteria and return multiple matches

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

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

=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, 0, 0), ROWS($A$1:A1)))

Explaining array formula (Return values vertically)

Step 1 - Identify cells equal to the criterion

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

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

becomes

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

becomes

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

becomes

{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, Functions, 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, Functions

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, Functions

Step 4 - Return corresponding value

In Cell C8:

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

becomes

=INDEX({1.5,2,1,1.7,3}, 1)

and returns $1.50

In Cell C9:

=INDEX($C$3:$C$7,4) is $1,70

Recommended article:

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Excel, Functions, Index

Download Excel *.xlsx file

How to return multiple values vertically.xlsx

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)))

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.

These posts show you how to extract records using two conditions: Filter records between two dates and Extract all rows from a range that meet criteria in one column

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

If you are looking for duplicate records, navigate to: Find duplicate records

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

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

Download *.xlsx file
Vlookup-return-multiple-records.xlsx

How to create an array formula

  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.

How to remove #num errors

Array formula:

=IFERROR(array_formula, "")

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

Return multiple values vertically or horizontally (vba)

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)

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

Array formula in cell C14:D14:

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

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

Download *.xlsm file

Vlookup-vba.xlsm

Recommended 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, User defined functions (udf), VBA, Vlookup

The following article lets you extract email addresses from a cell range:

Excel udf: Filter emails from an excel range

This udf extracts all words containing a specified string. Example, Cell range B1:M50 contains random sentences. I have inserted some […]

Comments(4) Filed in category: Excel, User defined functions (udf)