05-Best-Ways_VLOOKUP-Multiple-Criteria-in-Excel_1

05 Best Ways: VLOOKUP Multiple Criteria in Excel

By

VLOOKUP Multiple Criteria means VLOOKUP formula searches the lookup_value from the multiple columns or multiple cells or multiple ranges and retrieves a value based on the all criteria matches.

VLOOKUP function is one of the most used and most powerful functions for lookup in Advanced Excel. But one of the limitations of this function is it only works for looking up data having a single criterion. Means VLOOKUP formula searches the lookup_value against the first column (single criterion), not from the multiple columns (multiple criteria).

There are 4 best methods to retrieve values from multiple criteria:

  • VLOOKUP Multiple Criteria in Excel: with VLOOKUP & CONCATENATE functions
  • VLOOKUP Multiple Criteria in Excel: with VLOOKUP, CONCATENATE & MATCH functions (2-D Lookup)
  • VLOOKUP Multiple Criteria in Excel: with VLOOKUP & CHOOSE functions
  • Alternative of VLOOKUP Multiple Criteria in Excel: with INDEX MATCH Multiple Criteria (Array Formula)
  • Alternative of VLOOKUP Multiple Criteria in Excel: with INDEX MATCH INDEX Multiple Criteria (Non-Array Formula)
VLOOKUP Multiple Criteria in Excel_example
Figure: VLOOKUP Multiple Criteria in Excel_Example

Using VLOOKUP multiple criteria or VLOOKUP multiple values helpful when 

• the values to be retrieved based on multiple criteria matches;

• the dataset has the duplicate criteria.

 


I. VLOOKUP Multiple Criteria in Excel: with VLOOKUP & CONCATENATE functions


If one value is in the first column and the second value is the second column, it will not able to find the match. In these circumstances, we can include an additional column concatenating the first and second (or any other column) to make a unique single criterion.

SYNTAX:

Syntax of VLOOKUP Multiple Criteria in Excel with VLOOKUP & CONCATENATE functions

STEPS:

VLOOKUP Multiple Criteria in Excel with VLOOKUP & CONCATENATE functions
Figure: VLOOKUP Multiple Criteria in Excel with VLOOKUP & CONCATENATE functions

• Step 1:

Insert a Helper column before the table_array, it is mandatory for using the VLOOKUP function.

 

• Step 2: 

Using the CONCATENATE() function in the helper column to make a unique criterion.

(i) Either use ampersand (&) sign to concatenate =C3 &*& D3

(ii) Or, use the CONCATENATE() function =CONCATENATE(C3, “*“, D3)

Copy the formula till the end of the range. Please note that we use an asterisk symbol “*” as the separator for concatenating. Because in some cases, two different criteria give the same result after combination.

Use of separator to concatenate inside vlookup

• Step 3:

Select the cell where to get the result of VLOOKUP Multiple Criteria (i.e., J3).

In this cell, press equality “=” sign to start formula and just type a few letters ‘=vlo….‘ and select the VLOOKUP function from the given suggestion list with the help of a down arrow (↓), if required. 

Syntax of the VLOOKUP function_1

Then press the ‘Tab’ key, VLOOKUP syntax appears with the open parenthesis. 

Syntax of the VLOOKUP function

• Step 4:

Select the lookup_value from multiple columns, i.e., from H3 and I3 columns, but using the ampersand (&) sign for concatenating them.  Additionally, we use an asterisk symbol “*” as the separator for concatenating.

Then fix the Column address by pressing three times the F4 key. Thus the range converts from the relative to the mixed cell reference where it indicates the absolute column and relative row

As a result, while the formula is copied to the right side or horizontally, the column address does not change but the row address changes accordingly.

=VLOOKUP ($H3 & *& $I3

• Step 5:

Select the table_array which starts from Helper column (i.e., from $A$3) and extends the selection till the end of the range. Thus, the range of the table_array is A3:F20.

Select the range and makes it absolute from relative cell reference by pressing once F4 key. As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.

=VLOOKUP ($H3 & *& $I3, $A$3:$F$20

• Step 6: 

Then put the col_index_num which is the count of columns between the lookup column (i.e., the helper column) and the answer column (i.e., the Total Sales ($) column) and the value of columns count is 6. 

=VLOOKUP ($H3 & *& $I3, $A$3:$F$20, 6

• Step 7:

We are looking for an exact match, thus put the last argument, (i.e., the range_lookup), as zero (0) or FALSE.

=VLOOKUP ($H3 & *& $I3, $A$3:$F$20, 6, 0

• Step 8:

Finally, press Enter to accept the formula. Formula ends by default and closes the last parenthesis as well. Copy the formula end of the range. The complete formula looks like 

=VLOOKUP ($H3 & *& $I3, $A$3:$F$20, 6, 0)

As a result, we get the result in Total Sales ($) for the first instance is 7,510.

• Step 9: Convert all the Formulas into Values

We should convert all the formulas into the values in two ways:  

(i) Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box:

Copy the cells or ranges (i.e., J3:J4) with formula by using the Excel shortcut Ctrl+C  ➪ select the ‘Values and number formats‘ option in the Paste Special dialog box by Excel shortcut Alt+E+S+U / Alt+Ctrl+V+U  ➪ press Enter or click on OK

Convert all the Formulas into Values_ALT+E+S+U or ALT+Ctrl+V+U
Figure: Convert all the Formulas into Values_ALT+E+S+U or ALT+Ctrl+V+U

(ii) Using the ‘Values‘ option in the ‘Paste Special’ dialog box: 

Copy the cells or ranges (i.e., J3:J4) with formula by using the Excel shortcut Ctrl+C  ➪ select the ‘Values‘ option in the Paste Special dialog box applying the Excel shortcut Alt+E+S+V or Alt+Ctrl+V+V  ➪ press Enter or click on OK

Figure: Convert all the Formulas into Values_using 'Values' option_ALT+E+S+V or ALT+Ctrl+V+V

Figure: Convert all the Formulas into Values_using ‘Values’ option_ALT+E+S+V or ALT+Ctrl+V+V

 


II. VLOOKUP Multiple Criteria in Excel: with VLOOKUP, CONCATENATE & MATCH functions (2-D Lookup)

 

The VLOOKUP, CONCATENATE & MATCH function is the best way of the Vlookup Multiple Criteria.

The MATCH function is used to return the position of the item, not the actual item. Whereas the VLOOKUP function retrieves the cell content or item.

The VLOOKUP and MATCH functions work in two-dimensionally, which means the VLOOKUP function looks for vertically or row-wise, whereas the MATCH function looks for horizontally or column-wise and returns a value based on the two criteria (both row and column criteria). This type of lookup in Excel is called the two-dimensional lookup or the 2-D lookup

SYNTAX:

Syntax of VLOOKUP with Multiple criteria with VLOOKUP & CONCATENATE & MATCH function

STEPS:

VLOOKUP Multiple Criteria in Excel with VLOOKUP, CONCATENATE & MATCH functions (2-D Lookup)

• Step 1:

Using the Helper Column before the table_array, it is mandatory for using the VLOOKUP function.

 

• Step 2:

Using the CONCATENATE() function in the helper column to make a unique criterion. 

  • Either use ampersand (&) sign to concatenate =C3 & “*” & D3
  • Or, using the CONCATENATE() function =CONCATENATE(C3, “*”, D3)

Copy the formula till the end of the range.

Please note that we use an asterisk symbol “*” as the separator for concatenating.

 

• Step 3:

Select the cell where to get the result of VLOOKUP Multiple Criteria (i.e., J3).

In this cell, press equality “=” sign to start formula and just type a few letters ‘=vlo….‘ and select the VLOOKUP function from the given suggestion list with the help of a down arrow (↓), if required. 

Syntax of the VLOOKUP function_1

Then press the ‘Tab’ key, VLOOKUP syntax appears with the open parenthesis 

Syntax of the VLOOKUP function

• Step 4:

Select the lookup values from multiple columns, i.e., from H3 and I3 columns, but using the ampersand (&) sign for concatenating them.  Additionally, we use an asterisk symbol “*” as the separator for concatenating.  

Then fix the Column address by pressing three times the F4 key. Thus the range converts from the relative to the mixed cell reference where it indicates the absolute column and relative row.

As a result, while the formula is copied to the right side or horizontally, the column address does not change but the row address changes accordingly.

=VLOOKUP ($H3 & *& $I3

 

• Step 5:

Select the table_array which starts from Helper column (i.e., from $A$3) and extends the selection till the end of the range. Thus, the range of the table_array is A3:F20.

Select the range and makes it absolute from relative cell reference by pressing once F4 key. As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.  

=VLOOKUP ($H3 & *& $I3, $A$3:$F$20

• Step 6:

The MATCH() function returns the position of an item within an array that matches a specific value.

Using the MATCH() function in place of col_index_num automatically updates the column number that makes the dynamic formula. 

Just type a few letters “mat…” within the VLOOKUP function in place of column_index_num and select the MATCH function from the given suggestion list with the help of a down arrow (↓), if required.

Remember that upper and lower case doesn’t matter for typing the function, Excel by default converts it into the upper case. 

Syntax of the MATCH function_01 Then press the ‘Tab’ key, by default MATCH syntax appears with an open parenthesis.

Syntax of the MATCH function

Complete the formula as follow as:   

=VLOOKUP ($H3 & *& $I3, $A$3:$F$20, MATCH(J$2, $A$2:$F$2, 0)

• J$2 = lookup_value reference to ‘Total Sales ($)’ and fixed the row address by pressing two times F4 Key. As a result, the lookup_value is converted from the relative cell reference to the mixed column cell reference, where it indicates the relative column and absolute row address.

As a result, while the formula is copied to the other cells, the row address does not change but the column address changes accordingly. 

• $A$2:$F$2 = lookup_array is a range where lookup_value found and fixed the range by pressing once the F4 key. Thus the range is converted to absolute from the relative cell reference.

As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.

• 0  = for an exact match in place of match_type, the last argument of the MATCH() function.

 

• Step 7:

We are looking for an exact match, thus put the last argument, (i.e., the range_lookup), as zero (0) or FALSE.  

=VLOOKUP ($H3 & *& $I3, $A$3:$F$20, MATCH(J$2, $A$2:$F$2, 0), 0

 

• Step 8:

Finally, press Enter to accept the formula. Formula ends by default and closes the last parenthesis as well. Copy the formula end of the range. 

The complete formula looks like:

=VLOOKUP ($H3 & *& $I3, $A$3:$F$20, MATCH(J$2, $A$2:$F$2, 0), 0)

 

As a result, we get the result in Total Sales ($) for the first instance is 7,510.  

 

• Step 9: Convert all the Formulas into Values

We should convert all the formulas into the values in two ways: 

(i) Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box by Excel shortcut Alt+E+S+U / Alt+Ctrl+V+U; Or

(ii) Using the ‘Values‘ option in the ‘Paste Special’ dialog box by Excel shortcut Alt+E+S+V / Alt+Ctrl+V+V.

 


III. VLOOKUP Multiple Criteria in Excel: VLOOKUP & CHOOSE functions

 

VLOOKUP and CHOOSE nested functions to make a formula that performs VLOOKUP multiple criteria in Excel. 

• VLOOKUP & CHOOSE function with Helper column makes a non-array formula 

• VLOOKUP & CHOOSE function without Helper column makes an array formula

 

A) VLOOKUP Multiple Criteria by the VLOOKUP & CHOOSE functions with Helper Column (Non-array Formula)

 

In the case of VLOOKUP multiple criteria in Excel, if we use VLOOKUP CHOOSE functions along with a Helper column will make a non-array formula. 

SYNTAX:

Syntax of VLOOKUP Multiple Criteria in Excel_VLOOKUP & CHOOSE functions with Helper Column (Non-array Formula)

Figure: Syntax of VLOOKUP Multiple Criteria in Excel_VLOOKUP & CHOOSE functions with Helper Column (Non-array Formula)

 

STEPS:

Steps of VLOOKUP Multiple Criteria in Excel VLOOKUP & CHOOSE functions_With Helper Column_Non-ARRAY formula

Figure: Steps of VLOOKUP Multiple Criteria in Excel VLOOKUP & CHOOSE functions_With Helper Column_Non-Array formula

• Step 1:

Insert a Helper Column before the table_array, it is mandatory for using the VLOOKUP function.   

 

• Step 2:

Using the CONCATENATE() function in the helper column to make a unique criterion. 

  • Either use ampersand (&) sign to concatenate =C3 & “*” & D3
  • Or, using the CONCATENATE() function =CONCATENATE(C3, “*”, D3)

Copy the formula till the end of the range.

Please note that we use an asterisk symbol “*” as the separator for concatenating.

 

• Step 3:

Select the cell where to get the result of VLOOKUP Multiple Criteria (i.e., J3).

In this cell, press equality “=” sign to start formula and just type a few letters ‘=vlo….‘ and select the VLOOKUP function from the given suggestion list with the help of a down arrow (↓), if required. 

Syntax of the VLOOKUP function_1

Then press the ‘Tab’ key, VLOOKUP syntax appears with the open parenthesis 

Syntax of the VLOOKUP function

• Step 4:

Select the lookup values from multiple columns, i.e., from H3 and I3 columns, but using the ampersand (&) sign for concatenating them.  Additionally, we use an asterisk symbol “*” as the separator for concatenating.  

Then fix the Column address by pressing three times the F4 key. Thus the range converts from the relative to the mixed cell reference where it indicates the absolute column and relative row.

As a result, while the formula is copied to the right side or horizontally, the column address does not change but the row address changes accordingly.

=VLOOKUP ($H3 & *& $I3

• Step 5:

In place of table_array, we use the CHOOSE function.

Just type a few letters ‘cho‘ and select the CHOOSE function from the given suggestion list with the help of a down arrow (↓), if required.  

Syntax of the CHOOSE function_1

Then press the ‘Tab’ key, CHOOSE syntax appears with the open parenthesis in the VLOOKUP function. 

Syntax of the CHOOSE function

Complete the CHOOSE formula as follows: 

=VLOOKUP ($H3 & *& $I3, CHOOSE({1,2}, $A$2:$A$20, $F$2:$F$20)

 

Inside the CHOOSE function, we can put the integer values in the curly brackets {} separated with comma (,) that will refer to a range of cells or columns likes CHOOSE({1,2}. 

Remember that according to the requirement, we can put 3, 4, 5… so on till 254.

Index number 1 always refers to the lookup column, after that any index number (2, 3, 4… so on) can refer to any column in our database either to the right side or to the left side of the lookup column.

Index number 1 always refers to the lookup column range (i.e., here is the Helper columnA2:A20. It is mandatory otherwise formula doesn’t work at all. Select the range and makes it absolute from relative cell reference by pressing once F4 key, like $A$2:$A$20. As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.

Index number 2 refers to the column range having answers (answer_range) i.e., F2: F20. Similarly, select the range and makes it absolute from relative cell reference by pressing once F4 key, like $F$2: $F$20.

 

• Step 6:

After closing the CHOOSE function as a table array, place a comma (,) after that and move to the next argument col_index_ num, the third argument of VLOOKUP function. 

Our answer value is present in the second column range (answer_range) which is referred by 2. So we put the value 2 in place of column_index_num. 

=VLOOKUP ($H3 & *& $I3, CHOOSE({1,2}, $A$2:$A$20, $F$2:$F$20), 2

 

• Step 7:

The last argument of VLOOKUP is range_lookup. As we are looking for an exact match, thus we consider the last argument as zero (0) or FALSE.  

=VLOOKUP ($H3 & *& $I3, CHOOSE({1,2}, $A$2:$A$20, $F$2:$F$20), 2,0

 

• Step 8:

Finally, press Enter to accept the formula. Formula ends by default and closes the last parenthesis as well. Copy the formula end of the range. 

The complete formula looks like:

=VLOOKUP ($H3 & *& $I3, CHOOSE({1,2}, $A$2:$A$20, $F$2:$F$20), 2,0)

As a result, we get the result in Total Sales ($) for the first instance is 7,510.  

 

• Step 9: Convert all the Formulas into Values

We should convert all the formulas into the values in two ways: 

(i) Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box by Excel shortcut Alt+E+S+U / Alt+Ctrl+V+U; Or

(ii) Using the ‘Values‘ option in the ‘Paste Special’ dialog box by Excel shortcut Alt+E+S+V / Alt+Ctrl+V+V.

 

B) VLOOKUP Multiple Criteria by the VLOOKUP & CHOOSE functions without Helper Column (Array Formula)

 

In the case of VLOOKUP multiple criteria in Excel, if we use VLOOKUP CHOOSE functions without a Helper column will make an Array formula.

SYNTAX:Syntax-of-VLOOKUP-Multiple-Criteria-in-Excel_VLOOKUP-&-CHOOSE-functions-without-Helper-Column-(array-Formula)

STEPS:

Steps of VLOOKUP Multiple Criteria in Excel_ VLOOKUP & CHOOSE functions without Helper Column (Array Formula)

Figure: Steps of VLOOKUP Multiple Criteria in Excel_ VLOOKUP & CHOOSE functions without Helper Column (Array Formula)

• Step 1:

Select the cell where to get the result of VLOOKUP Multiple Criteria (i.e., I3).

In this cell, press equality “=” sign to start formula and just type a few letters ‘=vlo….‘ and select the VLOOKUP function from the given suggestion list with the help of a down arrow (↓), if required. 

Syntax of the VLOOKUP function_1

Then press the ‘Tab’ key, VLOOKUP syntax appears with the open parenthesis 

Syntax of the VLOOKUP function

• Step 2:

Select the lookup values from multiple columns, i.e., from G3 and H3 columns, but using the ampersand (&) sign for concatenating them.  Additionally, we use an asterisk symbol “*” as the separator for concatenating.  

Then fix the Column address by pressing three times the F4 key. Thus the range converts from the relative to the mixed cell reference where it indicates the absolute column and relative row.

As a result, while the formula is copied to the right side or horizontally, the column address does not change but the row address changes accordingly.

=VLOOKUP ($G3 & *& $H3

• Step 3:

In place of table_array, we use the CHOOSE function.

Just type a few letters ‘cho‘ and select the CHOOSE function from the given suggestion list with the help of a down arrow (↓), if required.  

Syntax of the CHOOSE function_1

Then press the ‘Tab’ key, CHOOSE syntax appears with the open parenthesis in the VLOOKUP function. 

Syntax of the CHOOSE function

Complete the CHOOSE formula as follows: 

=VLOOKUP ($G3 & *& $H3, CHOOSE({1,2}, $B$2:$B$20 & “*” & $C$2:$C$20, $E$2:$E$20)

 

Inside the CHOOSE function, we can put the integer values in the curly brackets {} separated with comma (,) that will refer to a range of cells or columns likes CHOOSE({1,2}. 

Remember that according to the requirement, we can put 3, 4, 5… so on till 254.

Index number 1 always refers to the lookup column, after that any index number (2, 3, 4… so on) can refer to any column in our database either to the right side or to the left side of the lookup column.

Index number 1 always refers to the lookup column range. We make two criteria ranges into a single lookup range.  For example, there are two ranges B2:B20 and C2:C20 and make them concatenate with an ampersand sign ($). In between them, we use an asterisk symbol “*” as a separator, like B2:B20 & “*” & C2:C20.

It is a mandatory step otherwise formula doesn’t work at all. Select the range and makes it absolute from relative cell reference by pressing once F4 key, like $B$2:$B$20 & “*” & $C$2:$C$20. As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.  

Index number 2 refers to the column range having answers (answer_range) i.e., E2: E20. Similarly, select the range and makes it absolute from relative cell reference by pressing once F4 key, like $E$2: $E$20.

 

• Step 4:

After closing the CHOOSE function as a table array, place a comma (,) after that and move to the next argument col_index_ num, the third argument of VLOOKUP function. 

Our answer value is present in the second column range (answer_range) which is referred by 2. So we put the value 2 in place of col_index_num

=VLOOKUP ($G3 & *& $H3, CHOOSE({1,2}, $B$2:$B$20 & “*” & $C$2:$C$20, $E$2:$E$20),2

 

• Step 5:

The last argument of VLOOKUP is range_lookup. As we are looking for an exact match, thus we consider the last argument as zero (0) or FALSE.  

=VLOOKUP ($G3 & *& $H3, CHOOSE({1,2}, $B$2:$B$20 & “*” & $C$2:$C$20, $E$2:$E$20), 2, 0

 

• Step 6:

Since this is an array formula, press Ctrl+Shift+Enter to accept the formula, instead of just Enter. By default curly brackets {} placed before and after the formula.

Then the complete formula looks like

{=VLOOKUP ($G3 & *& $H3, CHOOSE({1,2}, $B$2:$B$20 & “*” & $C$2:$C$20, $E$2:$E$20), 2, 0)}

 

Only the copy-paste an array formula into the other cells, Excel does not allow at all. In this case, we drag the cell with the formula end of the range with the fill handle, it is a small square in the bottom-right corner of the selected cell.

As a result, we get the result in Total Sales ($) for the first instance is 7,510.  

 

• Step 7: Convert all the Formulas into Values

We should convert all the formulas into the values in two ways: 

(i) Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box by Excel shortcut Alt+E+S+U / Alt+Ctrl+V+U; Or

(ii) Using the ‘Values‘ option in the ‘Paste Special’ dialog box by Excel shortcut Alt+E+S+V / Alt+Ctrl+V+V.

 


IV. Alternative of VLOOKUP Multiple Criteria in Excel: with INDEX MATCH Multiple Criteria (Array Formula)

 

An alternative of VLOOKUP multiple criteria is using another array formula INDEX MATCH formula to retrieve values that meet multiple criteria.

SYNTAX of INDEX MATCH Formula:

Syntax of Alternative of VLOOKUP Multiple Criteria in Excel with INDEX MATCH Multiple Criteria (Array Formula)

STEPS of INDEX MATCH Formula:

 

Steps of Alternative of VLOOKUP Multiple Criteria in Excel with INDEX MATCH Multiple Criteria (Array Formula)

Figure: Steps of Alternative of VLOOKUP Multiple Criteria in Excel with INDEX MATCH Multiple Criteria (Array Formula)

• Step 1:

Select the cell where to get the result of INDEX MATCH multiple criteria (i.e., I3).

In this cell, press equality “=” sign to start formula and just type a few letters ‘=ind….‘ and select the INDEX function from the given suggestion list with the help of a down arrow (↓), if required. 

Syntax of the INDEX function_1
Then press the ‘Tab’ key, INDEX syntax appears with the open parenthesis.
Syntax of the INDEX function
The INDEX function always returns a value or item specified by the intersection of the row_number and column_number

array or reference is the one or several ranges, named range, or table;
row_num is the row number in the array (if omitted, column_num is required);
column_num is the column number in the array (if omitted, row_num is required);
area_num specifies which range from the reference argument to use. 
 
• Step 2: 

Select the array (answer range) inside the INDEX function. Here we are looking for a “Total Sales ($)” and it is found in the range E3:E20.

Fix the range (both column and row addresses) by pressing F4 once. Thus the range converts from the relative to the absolute cell reference. As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.

Thus the formula is written as:

=INDEX ($E$3:$E$20,

 

• Step 3: 

In place of INDEX row_num, we should use the MATCH function. MATCH returns the position of an item within an array that matches a specific value, which makes the dynamic formula.

The Syntax of the MATCH function is as follows: 

Syntax of the MATCH function

Lookup_value is the item to match. It can be a number, text string, a logical value, or a reference.

Lookup_array is the table or an array containing all of the values to search. 

Match_type is a number that specifies how the match will be applied. 

  • A match_type of zero (0) finds the first item in the array that is an exact match with the lookup_value.
  • To find the item closest to but less than the lookup_value, use a match_type of -1
  • To find the item closest to but greater than the lookup_value, use a match_type of 1.  
In the last two cases, the values in the lookup_array must be in ascending order for the MATCH function to work correctly. The match_type is optional and will default to 1 if omitted from the arguments.  

Just type a few letters “mat…” and select the MATCH function from the given suggestion list with the help of a down arrow (↓), if required.
Remember that upper and lower case doesn’t matter for typing the function, Excel by default converts it into the upper case.
 
Syntax of the MATCH function_01
Then press the ‘Tab’ key, by default MATCH syntax appears within the INDEX function.
Syntax of the MATCH function
 
Complete the formula as follow as:
   
=INDEX ($B$3:$B$12, MATCH(1, ($G3=$B$3:$B$20)*($H3=$C$3:$C$20), 0)
 

1 = lookup_value reference to the TRUE condition.

If the MATCH function meets a match based on the supplied criteria in a single column range return TRUE, otherwise FALSE.

But using the multiplication between multiple conditions, MATCH function returns 1 and 0, respectively.

1 = TRUE*TRUE

0 = (TRUE*FALSE) or (FALSE*FALSE).

 

($G3=$B$3:$B$20)*($H3=$C$3:$C$20) = in place of lookup_array, using two conditions with multiplication. Multiplying the conditions to get the number. 1 for TRUE and 0 for FALSE. 

• ($G3=$B$3:$B$20) – condition 1. Where the first criterion (i.e., Olivier) in cell G3 is looking for a match within a column range B3:B20 of a table or dataset. If the result matches return TRUE, otherwise FALSE.

($H3=$C$3:$C$20) – condition 2. Where the second criterion (i.e., FRA-250) in cell H3 is looking for a match within a column range C3:C20 of a table or dataset. If the result matches return TRUE, otherwise FALSE.

 

0  = for an exact match in place of match_type, the last argument of the MATCH() function.

 

• Step 4: 

In place of INDEX column_num, we place zero value (0) or omit, because we don’t know how many columns to move.  

⇒ If we place zero, the INDEX -MATCH formula looks like:

=INDEX ($B$3:$B$12, MATCH(1, ($G3=$B$3:$B$20)*($H3=$C$3:$C$20), 0), 0)

 

⇒ If we omit zero, the INDEX -MATCH formula looks like: 

=INDEX ($B$3:$B$12, MATCH(1, ($G3=$B$3:$B$20)*($H3=$C$3:$C$20), 0))

 

• Step 5: 

Since this is an array formula, press Ctrl+Shift+Enter to accept the formula, instead of just Enter. By default curly brackets {} placed before and after the formula.

Then the complete formula looks like

{=INDEX ($B$3:$B$12, MATCH(1, ($G3=$B$3:$B$20)*($H3=$C$3:$C$20), 0), 0)}

{=INDEX ($B$3:$B$12, MATCH(1, ($G3=$B$3:$B$20)*($H3=$C$3:$C$20), 0))}

 

Only the copy-paste an array formula into the other cells, Excel does not allow at all. In this case, we drag the cell with the formula end of the range with the fill handle, it is a small square in the bottom-right corner of the selected cell.

As a result, we get the result in Total Sales ($) for the first instance is 7,510.  

 

• Step 7: Convert all the Formulas into Values

We should convert all the formulas into the values in two ways: 

(i) Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box by Excel shortcut Alt+E+S+U / Alt+Ctrl+V+U; Or

(ii) Using the ‘Values‘ option in the ‘Paste Special’ dialog box by Excel shortcut Alt+E+S+V / Alt+Ctrl+V+V.

 


V. Alternative of VLOOKUP Multiple Criteria in Excel: with INDEX- MATCH INDEX Multiple Criteria (Non-Array Formula)

 

INDEX, MATCH and INDEX function combine to perform a non-array formula with multiple criteria. This is also the best alternative for VLOOKUP multiple criteria.

The formula is the same as the INDEX MATCH formula, but in this case, using another INDEX function within the MATCH function. As a result, the INDEX MATCH INDEX formula becomes a non-array formula. 

SYNTAX:

Syntax of Alternative of VLOOKUP Multiple Criteria in Excel with INDEX MATCH INDEX Multiple Criteria (Non-array Formula)

STEPS:

Steps of Alternative of VLOOKUP Multiple Criteria in Excel with INDEX MATCH INDEX Multiple Criteria (Non-array Formula)

Figure: Steps of Alternative of VLOOKUP Multiple Criteria in Excel with INDEX MATCH INDEX Multiple Criteria (Non-array Formula)

 
In this formula, we use another INDEX function in place of MATCH lookup_array and within the INDEX function, we place the conditions. As a result, it becomes a non-array formula.
 
=INDEX ($B$3:$B$12, MATCH(1, INDEX (($G3=$B$3:$B$20)*($H3=$C$3:$C$20), 0,1), 0)
 

Read-More-5

Sudipta Mondal
Follow AEF
Advance Excel Forum

05 Best Ways: VLOOKUP Multiple Criteria in Excel

Editor's Rating:
5

Leave a Comment

You may also like