Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value

Related Links:

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value.

---

Return MULTIPLE corresponding values for ONE Lookup Value

The Excel VLOOKUP Function searches for a value (ie. Lookup_value) in the first column of a table array and returns a value in the same row from another column in the table array. In case of multiple occurrences of the Lookup value, the function searches the first occurrence of the Lookup value, and returns the corresponding value in the same row from another column.

In case you want to return multiple corresponding values, for the one Lookup value which has multiple occurrences, we show how it can be done using INDEX, SMALL, IF & ROW excel functions, as follows.

Consider the table array (“A2:B8”), in which you want to lookup the value “Apples” in column A which has multiple occurrences, and return all corresponding values in column B.

Enter the lookup value “Apples” in cell A11. In cell B11, enter below formula, as an array formula (CTRL+SHIFT+ENTER), and copy it downward in the same column B, in 7 rows (ie. number of times as the number of records in the table array “A2:B8”. Multiple corresponding values (of the lookup value “Apples”) will get copied vertically, starting from cell B11 till B17. Refer Table 1.

=INDEX($B$2:$B$8, SMALL(IF( $A$11=$A$2:$A$8, ROW($A$2:$A$8)- ROW($A$2)+1), ROW(1:1)))   [Formula]

This formula acts in 4 steps or parts, as explained below:

Step 1: Determine Row Number of the First Record

Formula Part  =-ROW($A$2)+1   indicates the number (in -ive) of rows above the first record, wherein =ROW($A$2)  is the row number of the first record in the table array. This returns the value: -2 +1 = -1. NOTE: If the table array is (“B3:C9”), then change this formula to  =-ROW($B$3)+1.

Step 2: Determine Row Number of Lookup Value

Formula Part =IF($A$11=$A$2:$A$8, ROW($A$2:$A$8))  evaluates to: {FALSE,3, FALSE,FALSE, 6,FALSE,8}, wherein the numbers 3,6,8 indicate the worksheet row numbers containing “Apples”. Formula Part   =IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)- ROW($A$2)+1)  evaluates to:  {FALSE,2,FALSE, FALSE,5, FALSE,7}, wherein 2,5,7 indicate the relative row numbers in the table array containing “Apples”.

Step 3: Determine Position of Row Numbers (ascending order) containing Lookup Value

Formula Part   =ROW(1:1)   returns the number 1, and when the formula is copied vertically downwards, it will become =ROW(2:2), =ROW(3:3), …. and will return the values: (1,2,3,4,5,6,7). These numbers indicate the k position in the function SMALL(array,k), ie. the k-th smallest value in a data set.

Formula Part (entered as array formula)   =SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)- ROW($A$2)+1), ROW(1:1))  returns the 1st smallest, 2nd smallest and 3rd smallest position of the relative row numbers in the table array which contain the lookup value of “Apples”. Refer column C in Table 2, wherein row numbers {2, 5,7} are returned.

Step 4: Using INDEX Function to determine Corresponding Values of the Lookup Value

The INDEX function, INDEX(array,row_num,column_num), accounts for the array as “$B$2:$B$8”, and row_num in the table array as {2,5,7}, and returns the intersection (column B, row numbers 2,5,7) values of {$12, $19,$11}.

————————————————————————————————————————-

Removing #NUM! errors:

Option 1:

Cell A12 (Table 3) has the formula  =COUNTIF(A2:A8, A11)  which returns 3, which is the number of occurrences of the lookup value “Apples”.

We had earlier mentioned to copy the formula in cell B11 downward in the same column B, in 7 rows (ie. number of times as the number of records in the table array “A2:B8”. However, since the number of occurrences of the lookup value (“Apples”) is only 3, you need to copy the formula downward in only 2 more rows, so that the formula appears in 3 rows: cells B11 to B13. The #NUM! error appears only when we try to determine corresponding values of more than the actual occurrences (3 positions in this example) of the lookup value. Refer column C in Table 3.

Option 2:

Alternatively, you can use the IFERROR function, with the formula, to remove the the #NUM! error, as shown below.

=IFERROR(INDEX( $B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)- ROW($A$2)+1), ROW(1:1))),”” )  [Formula]

In the above example, use this formula in cell B11, as an array formula (CTRL+SHIFT+ENTER), and copy it downward in the same column B, in 7 rows (ie. number of times as the number of records in the table array “A2:B8”. Refer column C in Table 4.

————————————————————————————————————————–

Make Vlookup value case-sensitive in the above example.

VLOOKUP function searches for a value (lookup value) in the first column of a table array and returns the corresponding value in the same row from another column in the table array. The lookup value which the function searches, is not case-sensitive. Similarly, in the above example, the lookup value (“Apples”) is not case sensitive, and it will not make a difference if the table array mentions “apples”, “APPLES” or “Apples”. To make the lookup value case-sensitive in the above example, combine the EXACT function in the formula.

EXACT function Syntax:  EXACT(text1,text2). Text1   is the first text string. Text2   is the second text string. EXACT compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. It is case-sensitive.

=INDEX($B$2:$B$8, SMALL(IF(EXACT($A$11,$A$2:$A$8), ROW($A$2:$A$8)- ROW($A$2)+1), ROW(1:1)))   [Formula]

In the above example, use this formula in cell B11, as an array formula (CTRL+SHIFT+ENTER), and copy it downward in the same column B, in 7 rows (ie. number of times as the number of records in the table array “A2:B8”. Refer Table 5.

————————————————————————————————————————-

SUM of MULTIPLE corresponding values returned by ONE Lookup Value

We have shown above how to get multiple corresponding values, of one Lookup Value. If you want the sum total of these multiple corresponding values, in one cell, there are multiple ways of doing this, as shown below. With reference to the example in Table 1:

=SUMPRODUCT(($A$11=$A$2:$A$8)*( $B$2:$B$8))  [Formula]

Returns the value $42, which is the sum of $12, $19 & $11.

=SUMPRODUCT($B$2:$B$8* IF($A$11=$A$2:$A$8,1))  [Formula]

Enter as an array formula (CTRL+SHIFT+ENTER). Returns the value $42, which is the sum of $12, $19 & $11.

=SUMIF($A$2:$A$8,$A$11, $B$2:$B$8)  [Formula]

Returns the value $42, which is the sum of $12, $19 & $11.

————————————————————————————————————————

Return MULTIPLE corresponding values for ONE Lookup Value, Horizontally, in one Row

In the above example, we had mentioned to enter the array formula, in cell B11, and copy it downward in the same column B, in 7 rows (ie. number of times as the number of records in the table array “A2:B8”. Multiple corresponding values (of the lookup value “Apples”) will get copied down vertically, starting from cell B11 till B17. To get the multiple corresponding values horizontally, in one row, just make one change in the formula, by replacing “ROW(1:1)” to “COLUMN(A1)”, and then copy the formula horizontally in the same row to the right columns, from Cell B11 to H11, in 7 columns (Refer Table 6). Below is the updated formula to be entered in cell B11.

=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)- ROW($A$2)+1), COLUMN(A1)))  [Formula]

You have seen above that  =ROW(1:1)   returns the number 1, and when the formula is copied vertically downwards, it becomes  =ROW(2:2), =ROW(3:3), … and returns the numbers 2,3 , … Similarly,  =COLUMN(A1)   returns the number 1, and when copied horizontally to the right columns, it becomes COLUMN(B1), COLUMN(C1), …. and returns the numbers 2,3 , ….