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

Return multiple values vertically

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

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

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

Copy cell C8 and paste to cells below. If you are interested in how this formula works, read this: Explaining formula (Return values vertically)

Download *.xlsx files

How to return multiple values vertically.xlsx
Vlookup-dynamic-named-range.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.

Enter the formula 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, "")

Explaining array formula (Return values vertically)

Step 1 - Identify cells equal to the criterion

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

= (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.

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

becomes

{TRUE, FALSE, FALSE, TRUE, FALSE}

Step 2 - Create array containing row numbers

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

ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+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}

Step 3 - Filter row numbers equal to criterion

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

becomes

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

Step 4 - Return the k-th smallest row number

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

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

becomes

SMALL({1,"","",4,""}, 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 cell 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 - Return value in range

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

In Cell C8:

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

becomes

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

and returns $1.50

In Cell C9:

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

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

Check out these posts and learn more about vlookup.