How to return multiple values using vLookup in excel

vlookup-return-multiple-values-vba

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

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.

Download *.xlsx files

How-to-return-multiple-values-vertically.xlsx
Vlookup-dynamic-named-range.xlsx

Return multiple values horizontally

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

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

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)

vlookup-return-multiple-values-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
    hyperlinks-in-a-pivot-chart_prj-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

Courtesy: get digital help

Tagged:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

7 − 4 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.