How to return multiple values using vlookup in excel

How to return multiple values using vlookup in excel

Filed in Excel, Search/Lookup, Vlookup on Oct.25, 2009. Email This article to a Friend  

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

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
Vlookup - dynamic named range.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

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