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.

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

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

Return-multiple-values-horizontally.xlsx

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

Vlookup-return-multiple-records.xlsx

### How to create an array formula

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

Interested in learning powerful excel array formula and functions?
Check out my Advanced excel course.

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