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 this array formula, these array formulas are easier to understand and troubleshoot.

Table of Contents

  1. Return multiple values vertically
  2. Return multiple values horizontally
  3. Return multiple records
  4. Return multiple values vertically or horizontally (vba)
  5. Lookup across multiple sheets
  6. Vlookup – Return multiple unique distinct values in excel

Return multiple values vertically

This array formula is entered in cell C8. Then copy cell C8 and paste to cells below.

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

How to create an array formula
Download excel file
Vlookup.xls

(Excel 97-2003 Workbook *.xls)

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

How to create an array formula

Download excel file
Return multiple values horizontally.xls

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 A10:C12.

How to create an array formula

Download excel file
Vlookup - return multiple records.xlsx
(Excel 2007 Workbook *.xlsx)

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into 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 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

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)

Array formula in cell C14:D14:

=vbaVlookup(B8, B2:C6, 2, "h")

How to enter custom function array formula

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

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

Vlookup-vba3.xls

Recommended articles

Check out these posts and learn more about vlookup.