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

=VLOOKUP($G3& “*” &$H3,

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

*row_number*and

*column_number*.

**Arguments:**

**∴**

**array****or**is the one or several ranges, named range, or table;

*reference***∴**

*is the row number in the array (if omitted,*

**row_num***column_num*is required);

**∴**

*is the column number in the array (if omitted,*

**column_num***row_num*is required);

**∴**

*specifies which range from the*

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

*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+Ente**r 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:

*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);**

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

## Premium Courses on ed2go

**Advanced Microsoft Excel 2019/Office 365**

**

**

**

**

**4.9/5

**Microsoft Excel 2019 Certification Training (Voucher Included)**

**

**

**

**

**4.9/5

**Accounts Payable Specialist Certification with Microsoft Excel 2019 (Voucher Included)**

**

**

**

**

**4.9/5

**Microsoft Office Specialist 2019 (MOS) Certification Training (Vouchers Included)**

**

**

**

**

**4.9/5

**Microsoft Office Specialist 2013**

**

**

**

**

**4.9/5

**Certified Administrative Professional with Microsoft Office Specialist 2019 (Vouchers Included)**

**

**

**

**

**4.9/5

**Certified Internal Auditor with Microsoft Excel 2019**

Bestseller

**

**

**

**

**4.9/5

**Executive Assistant with Microsoft Office Specialist 2019 (Vouchers Included)**

**

**

**

**

**4.9/5

**Payroll Manager**

**

**

**

**

**4.9/5

## Premium Courses on FutureLearn Limited

Project Management: Human Resources and Leadership

Cloud Computing Practitioner with AWS

Data Analytics for Business with Tableau

Become a Finance Manager. Start learning today.

Monash University – Data Science: Data Driven Decision Making

Become a Project Manager. Start learning today.

## Premium Courses on Coursera

**Introduction to Data Analysis Using Excel**

**

**

**

**

**4.9/5

**Excel Fundamentals for Data Analysis**

**

**

**

**

**4.9/5

**Mastering Data Analysis in Excel**

**

**

**

**

**4.9/5

**Understanding and Visualizing Data with Python**

**

**

**

**

**4.9/5

**Advanced Data Science with IBM**

**

**

**

**

**4.9/5

**Predictive Analytics and Data Mining**

**

**

**

**

**4.9/5

**Artificial Intelligence Data Fairness and Bias**

**

**

**

**

**4.9/5

**Data Visualization & Dashboarding with R**

**

**

**

**

**4.9/5

**Managing, Describing, and Analyzing Data**

**

**

**

**

**4.9/5

## Premium Courses on Udemy

**Microsoft Excel Pivot Tables – Master Excel Pivot Tables!**

**

**

**

**

**4.7/5

**77-728 Microsoft Excel 2016 Expert Certification**

**

**

**

**

**4.6/5

**Ultimate Microsoft Excel Course: Beginner to Excel Expert**

**

**

**

**

**5/5

**Learn python**

**

**

**

**

**4.7/5

**Google Professional Cloud Data Engineer**

**

**

**

**

**4.6/5

**Data Analysis with Machine Learning & Data Visualization**

**

**

**

**

**5/5

**30+ BEST ADVANCE EXCEL COURSES | By Coursera, Udemy |**

**36+ BEST ADVANCED EXCEL COURSE ONLINE | By ed2go |**

**80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts**

**10 Examples of Text to Columns || How to Split Cells/Columns in Excel**

**04 Best Ways: How to Transpose Data in Excel**

**How to use VLOOKUP Function in Excel || Must know Do OR Dont’s ||**

**03 Best Ways: Excel REVERSE VLOOKUP | VLOOKUP to the left |**

**03 Best Ways: EXCEL DOUBLE VLOOKUP/ IFERROR VLOOKUP/ NESTED VLOOKUP**