VLOOKUP with Multiple Criteria and Multiple Results: 8 Examples

Method 1 – Using the Ampersand Operator to VLOOKUP with Multiple Criteria

Steps:

Create a column to represent both criteria in the same column. We created one and named it State-Product.

Select the first cell of the column you created. We selected cell D6.
In cell D6 use the following formula.

=B6&"-"&C6

The Ampersand Operator will join the texts and return the joined texts as a result.

Press ENTER to get the result.

Drag the Fill Handle to copy the formula.

We have copied the formula to all the other cells and combined both criteria in the same column.

Now, we will use the VLOOKUP function to find the Sales for the months of January, February, and March.

Select the cell where you want to find the Sales for January. We selected cell B22.
In cell B22 use the following formula.

=VLOOKUP(C17&"-"&C18,D6:G14,{2,3,4},FALSE))

Formula Breakdown

C17&”-“&C18 —-> The Ampersand Operator (&) will join the texts.
- Output: “Florida-Headphone”
VLOOKUP(C17&”-“&C18,D6:G14,{2,3,4},FALSE) —-> turns into
- VLOOKUP(“Florida-Headphone”,D6:G14,{2,3,4},FALSE) —-> The VLOOKUP function will find the matches for the lookup_value from column 2, 3, and 4 and return the Sales as an array.
  - Output: {800,1000,1200}

Press ENTER to get multiple results. If you use a version of Microsoft Excel older than Excel 2019, press CTRL+SHIFT+ENTER to get the results.

Note: You have to make sure that the lookup_value remains in the leftmost column of the table_array because that is where the function will look for the match.

Method 2 – Applying the CONCATENATE Function to VLOOKUP with Multiple Criteria and Return Multiple Results

Steps:

Create a column combining both criteria by following the steps from Method 1.

Select the cell where you want to find Sales for January. We selected cell B22.
In cell B22 use the following formula.

=VLOOKUP(CONCATENATE(C17,"-",C18),D6:G14,{2,3,4},FALSE)

Formula Breakdown

CONCATENATE(C17,”-“,C18) —-> The CONCATENATE function will combine the texts from cells C17 and C18.
- Output: “Texas-Router”
VLOOKUP(CONCATENATE(C17,”-“,C18),D6:G14,{2,3,4},FALSE) —-> turns into
- VLOOKUP(“Texas-Router”,D6:G14,{2,3,4},FALSE) —-> The VLOOKUP function will find the matches for the lookup_value from columns 2, 3, and 4 and return the Sales as an array.
  - Output: {1100,1600,700}

Press ENTER to get the results. If you use an older version of Microsoft Excel than Excel 2019, press CTRL+SHIFT+ENTER to get the results.

Method 3 – Using the CHOOSE Function to VLOOKUP with Multiple Criteria

Steps:

Select the cell where you want to find the Sales for January. We selected cell G10.
In cell G10 use the following formula.

=VLOOKUP(H5&"-"&H6,CHOOSE({1,2,3},B6:B14&"-"&C6:C14,D6:D14,E6:E14),{2,3},FALSE)

Formula Breakdown

H5&”-“&H6 —-> The Ampersand Operator will join the texts.
- Output: “Ohio-Router”
B6:B14&”-“&C6:C14 —-> The Ampersand Operator will join these 2 columns and return as 1 column.
- Output: {“Texas-Mobile”;”Florida-Headphone”;”Ohio-Headphone”;”Texas-Headphone”;”Florida-Mobile”;”Ohio-Mobile”;”Texas-Router”;”Florida-Router”;”Ohio-Router”}
CHOOSE({1,2,3},B6:B14&”-“&C6:C14,D6:D14,E6:E14) —-> turns into
- CHOOSE({1,2,3},{“Texas-Mobile”;”Florida-Headphone”;”Ohio-Headphone”;”Texas-Headphone”;”Florida-Mobile”;”Ohio-Mobile”;”Texas-Router”;”Florida-Router”;”Ohio-Router”},D6:D14,E6:E14) —-> The CHOOSE function will return a table containing 3 columns.
  - Output: {“Texas-Mobile”,1000,1200;”Florida-Headphone”,800,1000;”Ohio-Headphone”,1500,1400;”Texas-Headphone”,1600,1800;”Florida-Mobile”,1000,1100;”Ohio-Mobile”,1300,1000;”Texas-Router”,1100,1600;”Florida-Router”,1200,1500;”Ohio-Router”,1700,1200}
VLOOKUP(H5&”-“&H6,CHOOSE({1,2,3},B6:B14&”-“&C6:C14,D6:D14,E6:E14),{2,3},FALSE) —-> turns into
- VLOOKUP(“Ohio-Router”,{“Texas-Mobile”,1000,1200;”Florida-Headphone”,800,1000;”Ohio-Headphone”,1500,1400;”Texas-Headphone”,1600,1800;”Florida-Mobile”,1000,1100;”Ohio-Mobile”,1300,1000;”Texas-Router”,1100,1600;”Florida-Router”,1200,1500;”Ohio-Router”,1700,1200},{2,3},FALSE) —-> The VLOOKUP function will find the match for lookup_value from columns 2 and 3 and then return multiple results as an array.
  - Output: {1700,1200}

Press ENTER to get the results. If you use an older version of Microsoft Excel, press CTRL+SHIFT+ENTER to get the results.

Method 4 – Using MAX and VLOOKUP Functions with Multiple Criteria to Return Multiple Results

Steps:

Select the cell from where you want your multiple results to start. Here, I selected cell F6.
In cell F6 use the following formula.

=VLOOKUP(MAX(B6:B14),B6:D14,{2,3},FALSE)

Formula Breakdown

MAX(B6:B14) —->The MAX function will return the largest value among the cell range B6:B14.
- Output: 1800
VLOOKUP(MAX(B6:B14),B6:D14,{2,3},FALSE) —-> turns into
- VLOOKUP(1800,B6:D14,{2,3},FALSE) —-> The VLOOKUP function will look for a match for lookup_value and return multiple results from columns 2 and 3 as an array.
  - Output: {“Texas”,”Headphone”}

Press ENTER to get the results. If you use an older version of Microsoft Excel, press CTRL+SHIFT+ENTER to get the results.

Method 5 – Applying MATCH and VLOOKUP Functions for Multiple Criteria

Steps:

Select the cell from where you want to start your multiple results. We selected cell H5.
In cell H5, apply the following formula.

=VLOOKUP(G5,$B$6:$E$11,MATCH({"Texas","Florida"},$B$5:$E$5,0),FALSE)

Formula Breakdown

MATCH({“Texas”,”Florida”},$B$5:$E$5,0) —-> The MATCH function will return the relative position of the lookup_value in the lookup_array.
- Output: {2,4}
VLOOKUP(G5,$B$6:$E$11,MATCH({“Texas”,”Florida”},$B$5:$E$5,0),FALSE) —-> turns into
- VLOOKUP(G5,$B$6:$E$11,{2,4},FALSE) —-> The VLOOKUP function will look for a match for the lookup_value and return multiple results from columns 2 and 4 as an array.
  - Output: {1300,1200}

Press ENTER to get the results. If you use an older version of Microsoft Excel, press CTRL+SHIFT+ENTER to get the results.

Dag the Fill Handle to get the result.

See that we have copied the formula to the other cells and got multiple results.

Method 6 – Using IF & VLOOKUP Functions with Multiple Criteria and Multiple Results

Steps:

Select the cell where you want the Sales for January. We selected cell G10.
In cell G10, apply the following formula.

=VLOOKUP(H5,IF(H6=C6:C14,B6:E14,""),{3,4},FALSE)

Formula Breakdown

IF(H6=C6:C14,B6:E14,””) —-> The IF function will check if the value in cell H6 matches any value in cell range C6:C14. If it matches then the formula will return cell range B6:E14; otherwise it will return a blank.
- Output: {“”,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;”Texas”,”Router”,1100,1600;”Florida”,”Router”,1200,1500;”Ohio”,”Router”,1700,1200}
VLOOKUP(H5,IF(H6=C6:C14,B6:E14,””),{3,4},FALSE) —-> turns into
- VLOOKUP(H5,{“”,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;””,””,””,””;”Texas”,”Router”,1100,1600;”Florida”,”Router”,1200,1500;”Ohio”,”Router”,1700,1200},{3,4},FALSE) —-> The VLOOKUP function will look for a match for the lookup_value from the table_array and return multiple results from columns 3 and 4.
  - Output: {1100,1600}

Press ENTER to get the results. If you use an older version of Microsoft Excel than Excel 2019, press CTRL+SHIFT+ENTER to get the results.

Method 7 – Use the COUNTIF Function to VLOOKUP with Multiple Results

Steps:

Create a Helper Column. In this column, put unique names for all the products.

Select the first cell of the Helper Column. We selected cell B5.
In cell B5, use the following formula.

=C5&COUNTIF(C5:C13,C5)

In the COUNTIF function, we selected cell range C5:C13 as the range and cell C5 as the criteria. The function will return the number of cells that match the criteria. The Ampersand Operator will join the text and formula.

Press ENTER to get the result.

Drag the Fill Handle to copy the formula.

We copied the formula to all the other cells and got unique names for every Product.

Keep only the value to ignore the Circular Reference error.

Select the cell range. We selected cells B5:B13.
Copy the cell range by pressing CTRL+C on your keyboard.

Right-click on the selected range.
Select Values from the Paste Options.

See that the cells contain only values, not formulas.

Use the VLOOKUP function to find multiple results for a specific Product.

Select the cells from where you want to start the multiple results. We selected cell C16.
In cell C16, use the following formula.

=VLOOKUP(B16&ROW(A1:A3),B5:E13,3,FALSE)

Formula Breakdown

ROW(A1:A3) —-> The ROW function will return the row numbers of the selected cell range.
- Output: {1;2;3}
B16&ROW(A1:A3) —-> turns into
- B16&{1;2;3} —-> The Ampersand Operator will join the text with the formula.
  - Output: {“Mobile1″;”Mobile2″;”Mobile3”}
VLOOKUP(B16&ROW(A1:A3),B5:E13,3,FALSE) —-> turns into
- VLOOKUP({“Mobile1″;”Mobile2″;”Mobile3”},B5:E13,3,FALSE) —-> Here, the VLOOKUP function will find match for multiple lookup_values and return multiple results as an array.
  - Output: {“Ohio”;”Florida”;”Texas”}

Press ENTER to get the result. If you use an older version of Microsoft Excel than Excel 2019, press CTRL+SHIFT+ENTER to get the results.

We will show you how you can find all the Sales for Mobile.

Select the cell from where you want to start the Sales. We selected cell D16.
In cell D16, apply the following formula.

=VLOOKUP(B16&ROW(A1:A3),B5:E13,4,FALSE)

Formula Breakdown

ROW(A1:A3) —-> The ROW function will return the row numbers of the selected cell range.
- Output: {1;2;3}
B16&ROW(A1:A3) —-> turns into
- B16&{1;2;3} —-> The Ampersand Operator will join the text with the formula.
  - Output: {“Mobile1″;”Mobile2″;”Mobile3”}
VLOOKUP(B16&ROW(A1:A3),B5:E13,4,FALSE) —-> turns into
- VLOOKUP({“Mobile1″;”Mobile2″;”Mobile3”},B5:E13,4,FALSE) —-> The VLOOKUP function will find match for multiple lookup_values and return multiple results as an array.
  - Output: {1500;1000;1000}

Press ENTER to get the result. If you use an older version of Microsoft Excel, press CTRL+SHIFT+ENTER to get the results.

Method 8 – VLOOKUP with Multiple Criteria and Multiple Results

Steps:

Select the cell from where you want to start the multiple results. We selected cell H5.
In cell H5, use the following formula.

=IFERROR(INDEX($D$5:$D$14, SMALL(IF(1=((--($F$5=$B$5:$B$14)) * (--($G$5=$C$5:$C$14))), ROW($D$5:$D$14)-4,""), ROW()-4)),"")

Formula Breakdown

(–($F$5=$B$5:$B$14)) —-> The Double Unary Operator will return the True False values as Zeros and Ones.
- Output: {0;1;0;0;1;0;0;1;1;0}
(–($G$5=$C$5:$C$14)) —-> The Double Unary Operator will return the True False values as Zeros and Ones.
- Output: {0;1;1;0;1;0;0;0;1;1}
((–($F$5=$B$5:$B$14)) * (–($G$5=$C$5:$C$14))) —-> turns into
- ({0;1;0;0;1;0;0;1;1;0} * {0;1;1;0;1;0;0;0;1;1}) —-> These two arrays will be multiplied.
  - Output: {0;1;0;0;1;0;0;0;1;0}
ROW($D$5:$D$14)-4 —-> The ROW function will return the row numbers of the selected cell range and then subtract 4 from the numbers.
- Output: {1;2;3;4;5;6;7;8;9;10}
IF(1=((–($F$5=$B$5:$B$14)) * (–($G$5=$C$5:$C$14))), ROW($D$5:$D$14)-4,””) —-> turns into
- IF(1={0;1;0;0;1;0;0;0;1;0}, {1;2;3;4;5;6;7;8;9;10},””) —-> The IF function will check for the logical_test. If it is True then the function will return numbers from the array, otherwise it will return a blank.
  - Output: {“”;2;””;””;5;””;””;””;9;””}
ROW()-4 —-> The ROW function will return the row number of the cell it is in and then subtract 4 from the number.
- Output: {1}
SMALL(IF(1=((–($F$5=$B$5:$B$14)) * (–($G$5=$C$5:$C$14))), ROW($D$5:$D$14)-4,””), ROW()-4) —-> turns into
- SMALL({“”;2;””;””;5;””;””;””;9;””}, {1}) —-> The SMALL function will return a numeric value from the array based on the position in ascending order.
  - Output: {2}
INDEX($D$5:$D$14, SMALL(IF(1=((–($F$5=$B$5:$B$14)) * (–($G$5=$C$5:$C$14))), ROW($D$5:$D$14)-4,””), ROW()-4)) —-> turns into
- INDEX($D$5:$D$14, {2}) —-> The INDEX function will return the value in the given position of the array.
  - Output: “Merry”
IFERROR(INDEX($D$5:$D$14, SMALL(IF(1=((–($F$5=$B$5:$B$14)) * (–($G$5=$C$5:$C$14))), ROW($D$5:$D$14)-4,””), ROW()-4)),””) —-> turns into
- IFERROR(“Merry”,””) —-> The IFERROR function will return blank if any error is found.
  - Output: “Merry”

Press ENTER to get the result.

Drag the Fill Handle to other results for the same criteria.

We got all the names of the Sales Person who sells Headphones in Florida.

How to Combine VLOOKUP with Other Functions in Excel

Example 1 – Using SUM and VLOOKUP Functions

Steps:

Select the cell where you want to calculate the Bonus. We selected cell E6.
In cell E6, use the following formula.

=SUM((C6*VLOOKUP(C6,$G$5:$H$9,2,TRUE)),(D6*VLOOKUP(D6,$G$5:$H$9,2,TRUE)))

Formula Breakdown

VLOOKUP(C6,$G$5:$H$9,2,TRUE) —-> The VLOOKUP function will look for a match from the table_array and then return the result from column 2.
- Output: 0.03
VLOOKUP(D6,$G$5:$H$9,2,TRUE) —-> The VLOOKUP function will look for a match from the table_array and then return the result from column 2.
- Output: 0.05
(C6*VLOOKUP(C6,$G$5:$H$9,2,TRUE)) —-> turns into
- (C6*0.03) —-> The value in cell C6 will be multiplied by 0.03.
  - Output: 36
(D6*VLOOKUP(D6,$G$5:$H$9,2,TRUE)) —-> turns into
- (D6*0.05) —-> The value in cell D6 will be multiplied by 0.05.
  - Output: 75
SUM((C6*VLOOKUP(C6,$G$5:$H$9,2,TRUE)),(D6*VLOOKUP(D6,$G$5:$H$9,2,TRUE))) —-> turns into
- SUM(36,75) —-> The SUM function will return the summation of these values.
  - Output: 111

Press ENTER to get the result.

Drag the Fill Handle to copy the formula to the other cells.

See that I have copied the formula to the other cells and calculated the Bonus for every Sales Person.

Example 2 – Combining VLOOKUP with the SUMIF Function

Steps:

Select the cell where you want the Total Sales. We selected cell G10.
In cell G10, use the following formula.

=SUMIF($B$5:$B$14,VLOOKUP(F10,$F$5:$G$7,2,FALSE),$D$5:$D$14)

Formula Breakdown

VLOOKUP(F10,$F$5:$G$7,2,FALSE) —-> The VLOOKUP function will look for a match for the look_up value and return the Employee ID.
- Output: 22001
SUMIF($B$5:$B$14,VLOOKUP(F10,$F$5:$G$7,2,FALSE),$D$5:$D$14) —-> turns into
- SUMIF($B$5:$B$14,22001,$D$5:$D$14) —-> The SUMIF function will sum the values from sum_range that match the criteria.
  - Output: 3200