04 Best Ways_How to Use Multiple Columns VLOOKUP or Multiple VLOOKUP in Excel

04 Best Ways : How to Use Multiple Columns VLOOKUP in Excel

By

Multiple Columns VLOOKUP in Excel is an advanced level of the VLOOKUP formula where the formula is used once with a certain condition(s) in a cell and that allow retrieving matched data from a table/dataset, but after stretching the formula to the right-side (row-wise; horizontally) or down-side (column-wise; vertically) it works dynamically to retrieves data from the table against the matched criteria.

In the Corporate Sector, the common term of Multiple Columns Vlookup is Multiple VLOOKUP or Multiple LOOKUP or Matrix LOOKUP or VLOOKUP Multiple Columns.

Remember that, sometimes it makes a confusion between Multiple Criteria VLOOKUP and Multiple Columns VLOOKUP.

[ Table/ Dataset is a structured range that contains related data organized in row(s) or column(s) in a worksheet that increases the capability to manage and analyze information.]  


I. Basic Criteria of Multiple Columns VLOOKUP in Excel

Firstly, we select the entire database area (range) means entire table_area or table_array. More clearly, the area between the lookup column and the last column of the active database.

As a result, the selection range should not be expanded or diminished every time, need to change column number or column_index_num only.

Secondly, we try out to make the column number or column_index_num to be dynamic.

Thirdly, proper use of cell reference (relative, absolute, mixed) within first three VLOOKUP arguments (i.e., lookup_value, table_array, col_index_num). It makes a big sense to make a dynamic VLOOKUP formula.  Otherwise, the VLOOKUP formula returns an error.


II. Methods of Multiple Columns VLOOKUP in Excel

A single VLOOKUP formula with certain parameters is used to retrieve values/data from a table/dataset. In the following example, based on the list in cells A3:L21, to find out the sales value in cell P4 against the Sales Manager name mentioned in cell O4 and Sales Date mentioned in cell P3.

Select cell P4 by clicking on it or press F2 key or via the formula bar.

Assign the VLOOKUP formula or the INDEX formula.

Press Enter to apply the formula in cell P4.

Using cell references (relative, absolute and mixed according to the requirement).

Then copy (Ctrl+C) the cell P4 with formula and extends it to the right-side and down-side till the end of the range i.e., P4:R11.

 

Here we discuss 4 methodologies of Multiple Columns VLOOKUP or Multiple VLOOKUP in Excel:

(01) Simple Method: Needs to change the column_index_number manually.

(02) Advanced Method: Using the predefined Column number(s) and Cell References, making the dynamic formula.

(03) Matrix Method: Using the VLOOKUP and MATCH nested functions, making the dynamic formula.

(04) Matrix Method: Using the INDEX, MATCH and MATCH nested functions, making the dynamic formula.


(01) Simple Method: Needs to Change the column_index_number Manually

Multiple Columns VLOOKUP- Simple Method_1

■ Step1: For a basic understanding of Multiple Columns VLOOKUP or Multiple VLOOKUP in Excel, we go through the Simple Method at the beginning where the column number needs to change manually.

Cells range A3: L21 is the main database or master database. We retrieve data from this master database with the VLOOKUP formula.

Place an equality sign (=) in cell P4 and type ‘VLO…’, select VLOOKUP from the below suggestion list of Down Arrow key () and press ‘Tab’ key. VLOOKUP syntax appears with an open parenthesis.

Note: the upper or lower case does not matter in syntax, Excel by default considers it in upper case.

Multiple Columns VLOOKUP- Simple Method_2

At first, we want to retrieve the sales value against Sales Manager Mathew on dated 20-May-19. Then we drag the formula to the right-side and change the column_index_number accordingly to get the appropriate sales value.

As the column_index_number plays an important role in Multiple Columns VLOOKUP, so we count the columns manually between the lookup column and the result column. For example,

(i) For the first instance, the lookup column is Column B i.e., the column with Sales Manager. Whereas, the result column (retrieve column) is column D which contains the sales values on 20-May-2019. The number of columns between Column B and D is 3.

(ii) Similarly, for the second instance, the lookup column is Column B and the result column is Column I (i.e., 30-Jun-19). The difference between Column B and I is 8.

(iii) Similarly, for the third instance, the lookup column is Column B and the result column is Column L (i.e., 03-Jul-19). The difference between Column B and L is 11.

 

■ Step2: Select the cell O4 as lookup_value, the first argument of VLOOKUP. Cell O4 refers to the Sales Manager ‘Mathew’.

Then place a comma (,) moving to the next argument.

Multiple Columns VLOOKUP- Simple Method_3

■ Step3: As discussed, select the entire range as table_array i.e., the range is B3:L21. B3 is staring cell of the table and L21 is the last cell as well. That means, column B is the first column of the table and Column L is the last column in the table (or dataset).

Multiple Columns VLOOKUP- Simple Method_4

Why we select column B instead of column A? Because lookup values (i.e., Names of Sales Manager) are located in column B instead of column A.

Place a comma (,) to move to the next argument.

■ Step4: Put a value 3 as a column_index_number, the third argument of VLOOKUP. Column number 3 refers to EXCEL to move 3 columns right starting from lookup column.

For example, lookup column in our database is Column B and Excel retrieves data from Column D, just 3 columns right considering column B.

Multiple Columns VLOOKUP- Simple Method_5

Place a comma (,) to move to the next argument.

■ Step5: As we find an exact match, so put 0 (zero) or False in place of range_lookup, the fourth argument of VLOOKUP.

Multiple Columns VLOOKUP- Simple Method_6

■ Step6: Using of Cell References

Multiple Columns VLOOKUP- Simple Method_7

(i) This is an important section. At first, select the lookup_value and press F4 Key three times to convert from the relative cell reference to the mixed cell reference (where the column becomes absolute but the row remains relative).

Thus, it seems like $O4.

So in that case, when we copy the formula vertically (column-wise), the column addresses remain fixed, but row numbers simultaneously changed, like $O4, $P4, $Q4, $R4… so on.

(ii) Next, select the table_array and press F4 Key once to convert the range from the relative cell reference to the absolute cell reference.

Thus, the table_array seems to be $B$3:$L$21.

Multiple Columns VLOOKUP- Simple Method_8

As a result, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number do not change at all.

■ Step7: Finally, press ‘Enter’ to apply the formula in cell P4 and get the value as a result.

Multiple Columns VLOOKUP- Simple Method_9

 

■ Step8: Extending the formula to the Right-Side (Row-Wise or Horizontally): Alt+E+S+R / Alt+Ctrl+V+R

Multiple Columns VLOOKUP- Simple Method_10

Copy (Ctrl+C) the cell P4 with formula and extends the selection to the right-side (same row or horizontally) ➪ press Alt+E+S+R / Alt+Ctrl+V+R sequentially, which will select the ‘Formulas and number formats‘ option under the ‘Paste Special’ dialog box ➪ then click on ‘OK’ or press ‘Enter’.

■ Step9: After extending the formula to the right side, we found that the same value is copied in all cells because of the column_index_number does not change dynamically.

So in that case, we have to change the column_index_number manually.

Multiple Columns VLOOKUP- Simple Method_11

Edit the cell Q4 under the month 30-Jun-19 via the formula bar or press F2 key or by clicking on it ➪ change the column_index_number from 3 to 8 ➪ press ‘Enter‘ to accept the formula ➪ will get the correct result (figure as below).

Multiple Columns VLOOKUP- Simple Method_12

Similarly, edit the cell R4 under the month 03-Jul-19 via the formula bar or press F2 key or by clicking on it ➪ change the column_index_number from 3 to 11 ➪ press ‘Enter‘ to accept the formula ➪ will get the correct result (figure as below).

Multiple Columns VLOOKUP- Simple Method_13

■ Step10: Extending the Formula to the Entire Range: Alt+E+S+R / Alt+Ctrl+V+R

After making changes, copy the range P4:R4 with Excel shortcut Ctrl+C ➪ then select the entire range P4:R11 with Shift+ Arrow Key(s) ➪ after that, press sequentially Alt+E+S+R / Alt+Ctrl+V+R which will select the option ‘Formulas and number formats‘ under the ‘Paste Special’ dialog box ➪ Click on ‘OK‘ or press ‘Enter‘ to accept the formula. 

Multiple Columns VLOOKUP- Simple Method_14

Finally, we retrieve the desired values from multiple columns against the matched criteria by using single VLOOKUP. This is called Multiple Columns VLOOKUP or Multiple VLOOKUP, a special feature of VLOOKUP.

Press ‘Esc’ (Escape) key to cancel the copy command.

 

■ Merits of this Simple Method for performing Multiple Columns VLOOKUP in Excel:

Instead of using multiple times of VLOOKUPs, a single VLOOKUP can manage to retrieve values from different columns aginst the matched criteria. This method saves a lot of time of daily Excel users.

■ Demerits of this Simple Method:

Manually change the column_index_number every time when formula copied to other columns.


(02) Advanced Method: Using the Predefined Column number(s) & Cell References

In this section, we use an advanced method for performing the Multiple Columns VLOOKUP or Multiple VLOOKUP in Excel.

■ Step1: Before applying the VLOOKUP formula, need to mention the specific retrieving column number just above each column.

 

■ Step2: Place an equality sign (=) in cell P4 and type ‘VLO’, select VLOOKUP from the below suggestion list by Down Arrow key () and then press ‘Tab’ key. VLOOKUP syntax appears with an open parenthesis.

Then, Select the cell O4 as lookup_value, the first argument of VLOOKUP. Cell O4 refers to the name of a Sales Manager ‘Mathew’.

Multiple Columns VLOOKUP- Advanced Method_2

Place a comma (,) to move to the next argument.

■ Step3: Select the entire range (B4:L21 or B3:L21) as table_array i.e., start from the first cell of the lookup column (B3 or B4) to the end cell of an active dataset (L21).

Multiple Columns VLOOKUP- Advanced Method_3

Place a comma (,) to move to the next argument.

■ Step4: Now select the col_index_num as cell P2 (where we already mentioned the retrieve column number), the third argument of VLOOKUP. Follow the below figure:

Multiple Columns VLOOKUP- Advanced Method_4

Place a comma (,) to move to the next argument.

■ Step5: As we find an exact match, so put 0 (zero) or False in place of range_lookup, the fourth argument of VLOOKUP.

Multiple Columns VLOOKUP- Advanced Method_5

■ Step6: Using of Cell References

(i) This is an important section. At first, select the lookup_value and press F4 Key three times to convert from the relative cell reference to the mixed cell reference (where the absolute column but the relative row).

Thus, it seems like $O4.

Multiple Columns VLOOKUP- Advanced Method_6

So in that case, when we copy the formula vertically (column-wise), the column addresses remain fixed, but row numbers simultaneously changed, like $O4, $P4, $Q4, $R4… so on.

(ii) Next, select the table_array and press F4 Key once to convert the range from the relative cell reference to the absolute cell reference.

Thus, the table_array seems to be $B$4:$L$21 or $B$3:$L$21 (according to the selection of the range).

Multiple Columns VLOOKUP- Advanced Method_7

As a result, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number do not change at all.

(iii)  After that, select the col_index_num (i.e., column_index_number) and press F4 Key twice to convert it from the relative cell reference to the mixed cell reference (where the row becomes absolute but the column remains relative).

Thus, it seems like P$2.

Multiple Columns VLOOKUP- Advanced Method_8

So in that case, while copying the formula horizontally (row-wise), the row numbers remain fixed, but column addresses simultaneously changed, like P$2, Q$2, R$2, S$2 …so on.

■ Step7: Finally, press ‘Enter‘ to apply the formula in cell P4 and get the value as a result.

Multiple Columns VLOOKUP- Advanced Method_9

■ Step8: Extending the Formula to the Entire Range: Alt+E+S+R / Alt+Ctrl+V+R

After getting the result, copy the cell P4 with Excel shortcut Ctrl+C ➪ making a selection of the entire range with Shift+ Right arrow (),  Down arrow ( ) ➪ press sequentially Alt+E+S+R / Alt+Ctrl+V+R which will select the option ‘Formulas and number formats‘ under the ‘Paste Special’ dialog box ➪ Click on ‘OK’ or press ‘Enter’ to accept the formula.

Multiple Columns VLOOKUP- Advanced Method_10

Finally, we get the required values from multiple columns against the matched criteria by using a single VLOOKUP. This is an example of advanced Multiple Columns VLOOKUP or advanced Multiple VLOOKUP

Press ‘Esc’ (Escape) key to cancel the copy command.

■  Note:

We can formulate column numbers with MATCH function. The Syntax of the MATCH() function is:

Syntax of the MATCH function

But two things must remember before applying the formula:

(i)  Lookup_array should start from the lookup column. In the given example, lookup_array is B3:L3. Column B is the lookup column of VLOOKUP. Column L is the last column of the active dataset/table. 

Multiple Columns VLOOKUP- Advanced Method_VLOOKUP MATCH_1

(ii) Please keep in mind that there should not be any duplicate subject headings. If there is any duplicity in headings criteria, the MATCH function only retrieves the value against first matched criteria. 

Multiple Columns VLOOKUP- Advanced Method_VLOOKUP MATCH_2

■  Merits of this Advanced Method for performing Multiple Columns VLOOKUP:

1. This method saves a lot of time of daily Excel users in Corporate sectors, especially where need to consolidate multiple files from different users and system dump on a daily basis.  

2. No need for the subject heading.

3. No effect on duplicate subject headings, because we mention a specific column number on each case to retrieve data. This column number defines a specific column. So there has no relation with duplicate subject headings. 


(03) Matrix Method: Using the VLOOKUP & MATCH nested functions

In this section, VLOOKUP MATCH nested function allows performing Multiple Columns VLOOKUP or Multiple VLOOKUP in Excel. As this method performs a 2D lookup (two-dimensional lookup), so this method is called the Matrix Method.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_1

■ Step1: In the first step, place an equality sign (=) in cell P4 and type ‘VLO...’ and press ‘Tab’ key. VLOOKUP syntax appears with an open parenthesis.

Then, Select the cell O4 as lookup_value, the first argument of VLOOKUP. Cell O4 refers to the Sales Manager named by Mathew.

Place a comma (,) to move to the next argument.

■ Step2: Select the entire range (B3:L21) as table_array i.e., start from the first cell of the lookup column (B3) to the end cell (L21) of an active dataset/table.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_2

Place a comma (,) to move to the next argument.

■ Step3: We use the MATCH() function in place of col_index_num of updating the column number automatically according to the change of a column, so the VLOOKUP formula works dynamically.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_3

In place of the third argument, just type ‘MAT…’ and then press ‘Tab’ key. MATCH syntax appears with an open parenthesis.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_4

Within the MATCH function:

(i)  Select cell P3 as lookup_value, the first argument of MATCH() function. Cell P3 refers to a date, i.e., 20-May-19.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_5

Place a comma (,) to move to the next argument.

(ii) Select the range B3:L3 as lookup_array, the second argument of MATCH() function.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_6

Please keep in mind that the starting point (or starting column) of lookup_array should be the same as a lookup column of VLOOKUP. Column B is the lookup column of the VLOOKUP formula, that’s why we start the lookup_array from column B.

MATCH() function is a single array function means it works only in a single row or in a single column. As per requirement, we select a single row (range is B3:L3) instead of selecting the entire range.

(iii) As we find an exact match, so put 0 (zero) or False in place of match_type, the third argument of MATCH() function.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_7

Then close the fist parenthesis of MATCH function and place a comma (,) moving to the last argument of VLOOKUP function.

■ Step4: As we find an exact match, so put 0 (zero) or False in place of range_lookup, the fourth argument of VLOOKUP.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_8

■ Step5: Using of Cell References

(i) First, select the lookup_value and press F4 Key three times to convert from the relative cell reference to the mixed cell reference (where the column address is absolute but the row is relative).

Thus, it seems like $O4.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_9

So in that case, while copying the formula vertically (column-wise), the column addresses remain fixed, but row numbers simultaneously changed, like $O4, $P4, $Q4, $R4… so on.

(ii) In the next step, select the table_array and press F4 Key once to convert the range from the relative cell reference to the absolute cell reference.

Thus, the table_array seems to be $B$3:$L$21.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_10

As a result, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number do not change at all.

(iii)  Then, select the col_index_num (i.e., column_index_number) where the MATCH function has applied.

(a) Select the cell P3 as lookup_value, the first argument of MATCH function and press F4 Key twice to convert it from the relative cell reference to the mixed cell reference (where the row is absolute but the column address is relative).

Thus, it seems like P$3.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_11

Thus, while copying the formula horizontally (row-wise), the row number remains fixed, but the column address simultaneously changed, like P$3, Q$3, R$3, S$3 …so on.

(b) Then select the range B3:L3 as lookup_array, the second argument of MATCH function and press F4 key once to convert the range from the relative cell reference to the absolute cell reference.

Thus, the lookup_array looks like $B$3:$L$3.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_12

So, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number remain fixed means they do not change at all.

■ Step6: Finally, press Enter to apply the formula and Excel returns a warning messageWe found a typo in your formula and tried to correct it to:”, either we press ‘Enter’ or click ‘Yes’ to accept this correction. EXCEL by default closes the last parenthesis and returns the result.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_13

■ Step7: Extending the Formula to the Entire Range: Alt+E+S+R / Alt+Ctrl+V+R

This step is similar to before. After getting the result, copy the cell P4 with Excel shortcut Ctrl+C ➪ making a selection of the entire range with Shift+ Right arrow (),  Down arrow ( ) ➪ press sequentially Alt+E+S+R / Alt+Ctrl+V+R which will select the option ‘Formulas and number formats‘ under the ‘Paste Special’ dialog box ➪ Click on ‘OK’ or press ‘Enter’ to accept the formula.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_14

Finally, we get the desired values from multiple columns against the matched criteria with the help of a single VLOOKUP.

Multiple Columns VLOOKUP- Matrix Method_VLOOKUP MATCH function_15

Press ‘Esc’ (Escape) key to cancel the copy command.

 

■ Merits of the VLOOKUP MATCH Matrix Lookup method:

1. This method also saves a lot of time of daily Excel users.

2. No need for the subject heading.

3. There is an effect of duplicate headings, as MATCH() function is used. So keep notice on that. 


(04) Matrix Method: Using the INDEX, MATCH & MATCH nested functions

INDEX, MATCH and MATCH functions cumulatively make a nested formula which is the best alternative of Multiple Columns VLOOKUP or Multiple Lookup in Excel. As this method performs a 2D lookup (two-dimensional lookup), so this method is called the Matrix Method.

The INDEX function returns a specific value or the address of a specific value from within an array, table, or range.

 

■ The Syntax for the INDEX Function

Syntax of the INDEX function

The INDEX function’s arguments are as follows:

• array: A range

• row_num: A row number within the array

• col_num: A column number within the array

The INDEX function always returns a value specified by the intersection of the row_number and column_number. If the array is one dimensional, meaning it contains a single row per column, then both row_num  and column_num are not required.

The INDEX function is more frequently used with two-dimensional array or tables.  

The MATCH and INDEX functions are often used together to perform lookups. The MATCH function returns the relative position of a cell in a range that matches a specified value.

 

■ The Syntax for the MATCH Function

Syntax of the MATCH function

The MATCH function’s arguments are as follows:

• lookup_value: The value that matches in lookup_array. If match_type is 0 and the lookup_value is text, this argument can include wildcard characters asterisk (*) and question mark (?).

• lookup_array: The range being searched.

• match_type: An integer (-1, 0, or 1) that specifies how the match is determined.

If match_type is 1, MATCH finds the largest value less than or equal to lookup_value. (lookup_array must be in ascending order.)

If match_type is 0, MATCH finds the first value exactly equal to lookup_value.

 If match_type is -1, MATCH finds the smallest value greater than or equal to lookup_value. (lookup_array must be in descending order.) If we omit the match_type argument, this argument is assumed to be 1.

 

■ The Syntax for the INDEX, MATCH & MATCH Nested Function

 

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_Syntax

 

■ Step to Start

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_1

■ Step1: In the first step, place an equality sign (=) in cell P4 and type ‘ind…’, select INDEX by down arrow (if required) and press ‘Tab’ key. INDEX syntax appears with an open parenthesis.

Then, select the range B3:L21 as an array, the first argument of INDEX function.

Please keep in mind that we should take more attention to range selection. For example, here we select range B3:L21 instead of A3:L21. Because column B is the lookup_column where criteria exist. The cell B3 is the starting point or starting cell for the MATCH function.

Place a comma (,) to move to the next argument.

■ Step2: We use the first MATCH() function in place of row_num, the second argument of INDEX function, getting the row number against the matched criteria.

In this case, just type ‘MAT…’ and then press ‘Tab’ key. MATCH syntax appears with an open parenthesis.

Within the MATCH function:

(i)  Select cell O4 as lookup_value, the first argument of MATCH() function. Cell O4 refers to the Sales Manager named by ‘Mathew’.

Place a comma (,) to move to the next argument.

(ii) Select the range B3:B21 as lookup_array, the second argument of MATCH() function.

As discussed above, the cell B3 is the starting point (or starting column or lookup column) of INDEX function and MATCH function(s) as well.

MATCH() function is a single array function means it works only in a single row or in a single column. As row_number is required for the second argument of INDEX function, we select a single column (range is B3:B21) instead of selecting a row or the entire range.

(iii) As we find an exact match, so put 0 (zero) or False in place of match_type, the third argument of MATCH() function.

Then close the fist parenthesis of MATCH function and place a comma (,) moving to the third argument of the INDEX function.

 

■ Step3: We use the second MATCH() function in place of column_num, the third argument of INDEX function, getting the column number against the matched criteria.

In this case, just type ‘MAT…’ and then press ‘Tab’ key. MATCH syntax appears with an open parenthesis.

Within the MATCH function:

(i)  Select cell P3 as lookup_value, the first argument of MATCH() function. Cell P3 refers to a date, i.e., 20-May-19.

Place a comma (,) to move to the next argument.

(ii) Select the range B3:L3 as lookup_array, the second argument of MATCH() function.

As discussed above, the cell B3 is the starting point (or starting column or lookup column) of INDEX function and MATCH function(s) as well.

MATCH() function is a single array function means it works only in a single row or in a single column. As column_number is required for the third argument of INDEX function, we select a single row (range is B3:L3) instead of selecting a column or the entire range.

(iii) As we find an exact match, so put 0 (zero) or False in place of match_type, the third argument of MATCH() function.

■ Step4: Finally, close the fist parenthesis of MATCH function and INDEX function as well. Then press ‘Enter’ to accept the formula.

Otherwise, before closing the parentheses press ‘Enter’ to accept the formula and Excel returns a warning messageWe found a typo in your formula and tried to correct it to:”, either we press ‘Enter’ or click ‘Yes’ to accept this correction. EXCEL by default closes the last parentheses and returns the result.

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_2

■ Step5: Using of Cell References

(01) First, select the index_array and press F4 Key once to convert the range from the relative cell reference to the absolute cell reference.

Thus, the array seems to be $B$3:$L$21.

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_3

As a result, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number remain fixed means they do not change at all.

(02) Then move into the first MATCH function.

(a) Select the cell O4 as lookup_value, the first argument of the MATCH function and press F4 Key three times to convert it from the relative cell reference to the mixed cell reference (where the column address is absolute but the row number is relative).

Thus, it seems like $O4.

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_4

Thus, while copying the formula vertically (column-wise), the column address remains fixed, but the row number simultaneously changed, like $O4, $O5, $O6, $O7 …so on.

(b) Then select the range B3:B21 as lookup_array, the second argument of MATCH function and press F4 key once to convert the range from the relative cell reference to the absolute cell reference.

Thus, the lookup_array seems to be $B$3:$B$21.

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_5

So, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number remain fixed means they do not change at all.

(03) Then move into the second MATCH function.

(a) Select the cell P3 as lookup_value, the first argument of MATCH function and press F4 Key twice to convert it from the relative cell reference to the mixed cell reference (where the row is absolute but the column address is relative).

Thus, it seems like P$3.

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_6

Thus, while copying the formula horizontally (row-wise), the row number remains fixed, but the column address simultaneously changed, like P$3, Q$3, R$3, S$3 …so on.

(b) Then select the range B3:L3 as lookup_array, the second argument of MATCH function and press F4 key once to convert the range from the relative cell reference to the absolute cell reference.

Thus, the lookup_array looks like $B$3:$L$3.

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_7

So, while copying the formula horizontally (row-wise) or vertically (column-wise) in any direction, columns address and row number remain fixed means they do not change at all.

(04) Press ‘Enter’ to accept the formula.

■ Step6: Extending the Formula to the Entire Range: Alt+E+S+R / Alt+Ctrl+V+R

This step is similar to before. After getting the result, copy the cell P4 with Excel shortcut Ctrl+C ➪ making a selection of the entire range with Shift+ Right arrow (),  Down arrow ( ) ➪ press sequentially Alt+E+S+R / Alt+Ctrl+V+R which will select the option ‘Formulas and number formats‘ under the ‘Paste Special’ dialog box ➪ Click on ‘OK’ or press ‘Enter’ to accept the formula.

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_8

Finally, we get the desired values from multiple columns against the matched criteria with the help of a Matrix lookup (INDEX, MATCH and MATCH nested function).

Press ‘Esc’ (Escape) key to cancel the copy command.

Matrix Method_ Using the INDEX, MATCH & MATCH nested functions_9

 

Merits of the INDEX MATCH Matrix Lookup method:

This is the best alternative of VLOOKUP MATCH Matrix Lookup method for performing Multiple Columns VLOOKUP in Excel.


Read-More_4

05 Best Ways: VLOOKUP Multiple Criteria in Excel

How to use VLOOKUP Function in Excel | allows 03 Best Matches

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

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

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

90+ Best Excel Shortcut Keys and Hotkeys with CTRL Key

Follow AEF
Sudipta Mondal is a writer, author as well as founder of "Advance Excel Forum (AEF)". It is the best learning platform on Advance Excel.

According to Sudipta, "Advanced Excel is a very necessary modern tool in the advanced world that can only be achieved through a committed focus and self-analyzing skills".
Sudipta Mondal
Follow AEF

You may also like