05 BEST WAYS TO USE EXCEL VLOOKUP MULTIPLE CRITERIA

Excel VLOOKUP Multiple Criteria is an advanced feature applies in such conditions where data or value needs to be retrieved vertically based on multiple criteria matched. In detail, the VLOOKUP formula searches the lookup_value vertically in a dataset based on multiple criteria (means a number of conditions/criteria from the multiple columns or multiple cells or multiple ranges to form a unique lookup_value) and retrieves a value based on all criteria matches.

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

With the help of Excel VLOOKUP Multiple Criteria we can combine multiple criteria into a single criterion but in a specific way or method. 

In the given example, we want to retrieve the total sales ($) based on two criteria i.e., Project Manager and Project Code. 

In this tutorial we apply 5 best alternative methods to retrieve the value(s) based on multiple criteria:

The VLOOKUP multiple criteria or VLOOKUP multiple values helpful when 

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

• the dataset has duplicate criteria.

(01). EXCEL VLOOKUP MULTIPLE CRITERIA: WITH VLOOKUP & CONCATENATE FUNCTION

It is one of the limitations that the VLOOKUP function is not able to find the match based on the multiple criteria or multiple values. In these circumstances, we can include an additional column concatenating the first and second (or any other column) to make a unique single criterion. Then apply the VLOOKUP function to retrieve the value based on this single criterion.

➢ SYNTAX:

➢ STEPS TO START:

• Step 1: Insert a Helper column before the table_array (that is the starting of the dataset) and it is mandatory to create a single criterion for using the VLOOKUP function.

• Step 2: Then apply the CONCATENATE() function in the first cell of the helper column (i.e., the cell A3) to make a unique criterion. We can apply any of the below 02 methods for concatenation. 

➢ Either use the ampersand (&) symbol for concatenation, for example  =C3 & “*” & D3

➢ Or, use the CONCATENATE() function, for example  =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. Instead of the asterisk, we may use hyphen “-“, underscore “_”, slash “/” as a separator. 

• Step 3: Select the cell where to start the VLOOKUP formula and get the result of VLOOKUP Multiple Criteria (i.e., the cell J3).

After selecting the cell, place an equality “=” sign to start the formula and just type a few letters of VLOOKUP such as =vlo… and select the VLOOKUP function from the Excel auto-suggested function list with the help of a down arrow (↓), if required. 

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

• Step 4: Select the lookup_value, the first argument of the VLOOKUP function.  Select the multiple criteria from multiple cells or multiple columns, for example from the cells H3 and I3, and simultaneously, concatenate them with an ampersand (&) symbol. Moreover, we use a separator in the double quotations between the criteria, for example, the asterisk symbol “*”.

Then we fix the cell reference for getting the dynamic result. Select the lookup_value argument with the cursor and fix only the column addresses by pressing the F4 key thrice. Thus the range is converted from the relative to the mixed cell reference where it indicates the absolute column and the 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,

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., table_array.

• Step 5: Select the table_array, the second argument of the VLOOKUP function, which starts from the Helper column (i.e., from cell A3) and extends the selection till the end of the range. Thus, the range of the table_array is A3:F20.

Select the range with the cursor and makes it absolute from relative cell reference by pressing the F4 key once. 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,

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., col_index_num.

• Step 6: Then put the col_index_num, the third argument of the VLOOKUP function, 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 count value between the columns is 6. 

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

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., range_lookup.

• Step 7: The last argument of the VLOOKUP function is range_lookup. In this case, we are looking for an exact match, thus put the value zero (0) or FALSE.

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

Alternatively, 

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

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

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

Alternatively, 

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

The formula returns the result 7,510 in cell J3 as a Sales ($) value.

• Step 9: Convert all the Formulas into Values

Consequently, we can convert all the formulas into values either in two ways:  

(i) METHOD 1:  Using the ‘Values and number formats’ Option in the ‘Paste Special’ Dialog box:

Copy the cell or range of cells (i.e., from J3:J4) with the Excel shortcut Ctrl+C  ➪ then press Alt+E+S+U  (sequentially press Alt, E, S, U) or  Alt+Ctrl+V+U (press Alt+Ctrl+V, U) which will select the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box  ➪ press Enter or click OK.

(ii) METHOD 2:  Using the ‘Values’ Option in the ‘Paste Special’ Dialog box:

Copy the cell or range of cells (i.e., from J3:J4) with the Excel shortcut Ctrl+C  ➪ then press Alt+E+S+V  (sequentially press Alt, E, S, V) or  Alt+Ctrl+V+V (press Alt+Ctrl+V, then V) which will select the ‘Values‘ option in the ‘Paste Special’ dialog box  ➪ press Enter or click OK.

(02). EXCEL VLOOKUP MULTIPLE CRITERIA: WITH VLOOKUP & MATCH FUNCTIONS

The VLOOKUP, CONCATENATE & MATCH functions combined to make a nested formula which is considered as the best alternative of the Excel 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.

Both the VLOOKUP and MATCH functions combinedly work with two-dimensions, which means the VLOOKUP function searches value for vertically or row-wise, whereas the MATCH function searches for horizontally or column-wise and retrieves a value based on the two criteria (both row and column criteria). This type of Excel lookup is known as the two-dimensional lookup or the 2-D lookup. 

➢ SYNTAX:

➢ STEPS TO START:

• Step 1: Insert a Helper column before the table_array (that is the starting of the dataset) and it is mandatory to create a single criterion for using the VLOOKUP function.

• Step 2: Then apply the CONCATENATE() function in the first cell of the helper column (i.e., the cell A3) to make a unique criterion. We can apply any of the below 02 methods for concatenation. 

➢ Either use the ampersand (&) symbol for concatenation, for example  =C3 & “*” & D3

➢ Or, use the CONCATENATE() function, for example  =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. Instead of the asterisk, we may use hyphen “-“, underscore “_”, slash “/” as a separator.

• Step 3: Select the cell where to start the VLOOKUP formula and get the result of VLOOKUP Multiple Criteria (i.e., the cell J3).

After selecting the cell, place an equality “=” sign to start the formula and just type a few letters of VLOOKUP such as “=vlo…” and select the VLOOKUP function from the Excel auto-suggested function list with the help of a down arrow (↓), if required. 

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

• Step 4: Select the lookup_value, the first argument of the VLOOKUP function.  

Select the multiple criteria from multiple cells or multiple columns, for example from the cells H3 and I3, and simultaneously, concatenate them with an ampersand (&) symbol. Moreover, we use a separator in the double quotations between the criteria, for example, the asterisk symbol “*”.

Then we fix the cell reference for getting the dynamic result. Select the lookup_value argument with the cursor and fix only the column addresses by pressing three times the F4 key. Thus the range is converted from the relative to the mixed cell reference where it indicates the absolute column and the 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,

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., table_array.

• Step 5: Select the table_array, the second argument of the VLOOKUP function, which starts from the Helper column (i.e., from cell A3) and extends the selection till the end of the range. Thus, the range of the table_array is A3:F20.

Select the range with the cursor and makes it absolute from relative cell reference by pressing the F4 key once. 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,

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., col_index_num.

• 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 the col_index_num, the third argument of the VLOOKUP function, which automatically updates the column number that makes the formula dynamic. 

Just type a few letters of the MATCH function, for example, “mat…”, in place of column_index_num, and select the MATCH function from the given auto-suggested 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. 

 Then press the ‘Tab’ key, by default MATCH syntax appears with an open parenthesis and completes the required arguments.

Now the formula seems to be:  =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 the F4 key twice. 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 the F4 key once. 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.

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., range_lookup.

• Step 7: The last argument of the VLOOKUP function is range_lookup. In this case, we are looking for an exact match, thus put the value zero (0) or FALSE.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 till the end of the range. The complete formula looks like this: 

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

The formula returns the result 7,510 in cell J3 as a Sales ($) value.

• Step 9: Convert all the Formulas into Values

It is very good practice to convert all the unnecessary formulas into values, otherwise, the Excel file becomes heavy in size and sometimes formulas return an error due to the deletion of the source file.  

We can convert all the formulas into values either in two ways: 

➢ Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box by the keyboard shortcut Alt+E+S+U  (sequentially press Alt, E, S, U) or Alt+Ctrl+V+U (press Alt+Ctrl+V, U); 

➢ Alternatively, using the ‘Values‘ option in the ‘Paste Special’ dialog box by the keyboard Alt+E+S+V  (sequentially press Alt, E, S, V) or  Alt+Ctrl+V+V (press Alt+Ctrl+V, V).

(03). EXCEL VLOOKUP MULTIPLE CRITERIA: WITH VLOOKUP & CHOOSE FUNCTIONS

Both the VLOOKUP and CHOOSE functions combined to form a nested formula that performs Excel VLOOKUP multiple criteria. 

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

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

A) VLOOKUP MULTIPLE CRITERIA: VLOOKUP & CHOOSE FUNCTIONS WITH HELPER COLUMN (NON-ARRAY FORMULA)

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

➢ SYNTAX:

➢ STEPS TO START:

• Step 1: Insert a Helper column before the table_array (that is the starting of the dataset) and it is mandatory to create a single criterion for using the VLOOKUP function.

• Step 2: Then apply the CONCATENATE() function in the first cell of the helper column (i.e., the cell A3) to make a unique criterion. We can apply any of the below 02 methods for concatenation. 

➢ Either use the ampersand (&) symbol for concatenation, for example  =C3 & “*” & D3

➢ Or, use the CONCATENATE() function, for example  =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. Instead of the asterisk, we may use hyphen “-“, underscore “_”, slash “/” as a separator.

• Step 3: Select the cell where to start the VLOOKUP formula and get the result of VLOOKUP Multiple Criteria (i.e., the cell J3).

After selecting the cell, place an equality “=” sign to start the formula and just type a few letters of VLOOKUP such as “=vlo…” and select the VLOOKUP function from the Excel auto-suggested function list with the help of a down arrow (↓), if required. 

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

• Step 4: Select the lookup_value, the first argument of the VLOOKUP function.

Select the multiple criteria from multiple cells or multiple columns, for example from the cells H3 and I3, and simultaneously, concatenate them with an ampersand (&) symbol. Moreover, we use a separator in the double quotations between the criteria, for example, the asterisk symbol “*”.

Then we fix the cell reference for getting the dynamic result. Select the lookup_value argument with the cursor and fix only the column addresses by pressing the F4 key thrice. Thus the range is converted from the relative to the mixed cell reference where it indicates the absolute column and the 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,

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., table_array.

• Step 5: In place of the table_array, the second argument of the VLOOKUP function, we will apply the CHOOSE function.

The CHOOSE() function returns the specific value from a list of values supplied as arguments.

Just type a few letters of the CHOOSE function, for example, cho… and select the CHOOSE function from the given auto-suggested 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. 

Then press the ‘Tab’ key and as a result, the CHOOSE syntax appears with the open parenthesis. We need to fill up all the required arguments in the CHOOSE function.

After applying the CHOOSE formula it looks like this: 

=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 column)  A2:A20. It is mandatory otherwise formula doesn’t work at all. Select the range and makes it absolute from relative cell reference by pressing the F4 key once and the range looks 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 (is called answer_range answer_range) i.e., F2: F20. Similarly, select the range and makes it absolute from relative cell reference by pressing the F4 key once, and the range looks like $F$2: $F$20.

• Step 6: After closing the parenthesis of the CHOOSE function, place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., col_index_ num, the third argument of the VLOOKUP function. 

Be careful that our answer value is present in the second column range (is called answer_range i.e., F2:F20) which is referred to 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: Place a comma (,) and move to the last argument of the VLOOKUP function is range_lookup. In this case, we are looking for an exact match, thus put the value 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 till the end of the range. The complete formula looks like this: 

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

The formula returns the result 7,510 in cell J3 as a Sales ($) value.

• Step 9: Convert all the Formulas into Values

Always try to convert all the formulas in the dataset into values in any of the following two ways:

➢ Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box by the keyboard shortcut Alt+E+S+U  (sequentially press Alt, E, S, U) or Alt+Ctrl+V+U (press Alt+Ctrl+V, U); 

➢ Alternatively, using the ‘Values‘ option in the ‘Paste Special’ dialog box by the keyboard Alt+E+S+V  (sequentially press Alt, E, S, V) or  Alt+Ctrl+V+V (press Alt+Ctrl+V, V).

(B) VLOOKUP MULTIPLE CRITERIA: VLOOKUP & CHOOSE FUNCTIONS WITHOUT HELPER COLUMN (ARRAY FORMULA)

In the case of Excel VLOOKUP multiple criteria, if we use the VLOOKUP CHOOSE nested formula without a Helper column will make a non-array formula. 

➢ SYNTAX:

➢ STEPS TO START:

• Step 1: Select the cell where to start the VLOOKUP formula and get the result of VLOOKUP Multiple Criteria (i.e., the cell I3).

After selecting the cell, place an equality “=” sign to start the formula and just type a few letters of VLOOKUP such as =vlo… and select the VLOOKUP function from the Excel auto-suggested function list with the help of a down arrow (↓), if required. 

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

• Step 2: Select the lookup_value, the first argument of the VLOOKUP function.

Select the multiple criteria from multiple cells or multiple columns, for example from the cells G3 and H3, and simultaneously, concatenate them with an ampersand (&) symbol. Moreover, we use a separator in the double quotations between the criteria, for example, the asterisk symbol “*”.

Then we fix the cell reference for getting the dynamic result. Select the lookup_value argument with the cursor and fix only the column addresses by pressing the F4 key thrice. Thus the range is converted from the relative to the mixed cell reference where it indicates the absolute column and the 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,

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., table_array.

• Step 3: In place of the table_array, the second argument of the VLOOKUP function, we will apply the CHOOSE function.

The CHOOSE() function returns the specific value from a list of values supplied as arguments.

Just type a few letters of the CHOOSE function, for example, ‘cho…’ and select the CHOOSE function from the given auto-suggested 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. 

Then press the ‘Tab’ key and as a result, the CHOOSE syntax appears with the open parenthesis. We need to fill up all the required arguments in the CHOOSE function.

After applying the CHOOSE formula it looks like this: 

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

➢ In 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}. 

As per our 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 into a single range by concatenating 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 the F4 key once, 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 the F4 key once, such as $E$2:$E$20.

• Step 4: After closing the parenthesis of the CHOOSE function, place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., col_index_ num, the third argument of the VLOOKUP function. 

Our answer value is present in the second column range (is called answer_range i.e., E2:E20) which is referred to by 2. So we put the value 2 in place of column_index_num. 

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

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., range_lookup.

• 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 this:

{=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

Always try to convert all the formulas in the dataset into values any of the following two ways: 

➢ Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box by the keyboard shortcut Alt+E+S+U  (sequentially press Alt, E, S, U) or Alt+Ctrl+V+U (press Alt+Ctrl+V, U); 

➢ Alternatively, using the ‘Values‘ option in the ‘Paste Special’ dialog box by the keyboard Alt+E+S+V  (sequentially press Alt, E, S, V) or  Alt+Ctrl+V+V (press Alt+Ctrl+V, V).

(04). INDEX MATCH MULTIPLE CRITERIA (ARRAY FORMULA)

The INDEX MATCH multiple criteria is an alternative of VLOOKUP multiple criteria and it is an array formula that retrieves values based on multiple criteria.

➢ SYNTAX::

➢ STEPS TO START:

• Step 1:

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

After selecting the cell, place an equality “=” sign to start the formula and just type a few letters of INDEX function such as ‘=ind….’ and select the INDEX function from the given auto-suggested function list with the help of a down arrow (↓), if required. 

Then press the ‘Tab’ key, INDEX syntax appears with the open parenthesis.  The INDEX function always returns a value or item specified by the intersection of the row_number and column_number.    Arguments:   ∴ 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), the first argument of the INDEX function. In the given example, we are looking for “Total Sales ($)” and it is found in the range E3:E20, so the array would be E3:E20.

Fix the range (both column and row addresses) by pressing the F4 key once. As a result, the range is converted from the relative to the absolute cell reference which indicates that 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,

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., row_num.

• Step 3: In place of the INDEX row_num, the second argument of the INDEX function, we 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 for 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 of the MATCH function i.e., “mat…” and select the MATCH function from the Excel provided below suggestion list with the help of a down arrow (↓), if required. Please keep in mind that the upper and lower case doesn’t matter for typing the function, Excel by default converts it into the upper case.    After selecting the function, press the ‘Tab’ key and as a result, the MATCH syntax appears with an open parenthesis.  The formula is written as: =INDEX ($E$3:$E$20, 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, the 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 the 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 the match_type, the last argument of the MATCH() function.

Place a comma (,) which indicates the completion of the current argument and pass a command to move to the next argument i.e., column_num.

• Step 4: In place of the 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 seems to be:

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

⇒ If we omit zero, the INDEX MATCH formula seems to be: 

=INDEX ($E$3:$E$20, 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.

Finally, the complete formula seems to be:

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

Alternatively, 

{=INDEX ($E$3:$E$20, 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 6: Convert all the Formulas into Values

It is very good practice to convert all the unnecessary formulas into values, otherwise, the Excel file becomes heavy in size and sometimes formulas return errors due to the deletion of a source file.  

We can convert all the formulas into values either in two ways:  

➢ Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box by the keyboard shortcut Alt+E+S+U  (sequentially press Alt, E, S, U) or Alt+Ctrl+V+U (press Alt+Ctrl+V, U); 

➢ Alternatively, using the ‘Values‘ option in the ‘Paste Special’ dialog box by the keyboard Alt+E+S+V  (sequentially press Alt, E, S, V) or  Alt+Ctrl+V+V (press Alt+Ctrl+V, V).

(05). INDEX MATCH MULTIPLE CRITERIA (NON-ARRAY FORMULA)

INDEX, MATCH, and INDEX functions 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, we add another INDEX function within the MATCH function. As a result, the INDEX MATCH INDEX formula becomes a non-array formula. 

➢ SYNTAX:

➢ STEPS TO START:

In this formula, we add another INDEX function in place of the MATCH lookup_array to avoid the array. So, the INDEX, MATCH, and INDEX nested formula working as a non-array formula. =INDEX ($E$3:$E$20, MATCH(1, INDEX ($G3=$B$3:$B$20)*($H3=$C$3:$C$20), 0, 1), 0) It is very good practice to convert all the unnecessary formulas into values, otherwise, the Excel file becomes heavy in size and sometimes formulas return errors due to the deletion of a source file.  

We can convert all the formulas into values either in two ways: 

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

Copy the cell or range of cells with the Excel shortcut Ctrl+C  ➪ then press Alt+E+S+U  (sequentially press Alt, E, S, U) or  Alt+Ctrl+V+U (press Alt+Ctrl+V, U) which will select the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box  ➪ press Enter or click OK.

(ii) METHOD 2:  Using the ‘Values’ Option in the ‘Paste Special’ dialog box:

Copy the cell or range of cells with the Excel shortcut Ctrl+C  ➪ then press Alt+E+S+V  (sequentially press Alt, E, S, V) or  Alt+Ctrl+V+V (press Alt+Ctrl+V, V) which will select the ‘Values‘ option in the ‘Paste Special’ dialog box  ➪ press Enter or click OK.