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:
Enter the formula as an array formula or use this regular but more complicated formula:
Copy cell C8 and paste to cells below.
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:
Enter the formula as an array formula or use this regular but more complicated formula:
Download *.xlsx file
Return-multiple-values-horizontally.xlsx
Return multiple records
Array formula in cell A10:
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:
Download *.xlsx file
Vlookup-return-multiple-records.xlsx
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula to formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to remove #num errors
Array formula:
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:
How to enter custom function array formula
- Select cell range C9:C11
- Type above custom function
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys
Array formula in cell C14:D14:
How to enter custom function array formula
- Select cell range C14:D14
- Type above custom function
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys
How to copy array formula to the next row
- Select cell range C14:D14
- Copy cell range
- Select cell range C15:D15
- Paste
Vba code
- Copy vba code below
- Press Alt + F11
- Insert a module in project explorer
- Paste into code window
- 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
Courtesy: get digital help