03 Best Ways Excel REVERSE VLOOKUP- VLOOKUP to the left

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

By

Excel Reverse VLOOKUP – is used to VLOOKUP to the left and is a useful formula in data analysis and MIS preparation.     

The VLOOKUP function searches value only to the right, but INDEX MATCH or VLOOKUP with CHOOSE function can look up in both directions from the lookup column – VLOOKUP to the left and VLOOKUP to the right. This feature in Excel is called both-way lookup or two-way lookup.

However, both of these nested functions able to retrieve values from the left of the lookup column are called reverse lookup or backward lookup.  That means a reverse lookup is a part of the both-way lookup. 

Both nested functions such as  INDEX MATCH and VLOOKUP and CHOOSE are more flexible than VLOOKUP. While reverse lookup is conducted by VLOOKUP (with CHOOSE function) is called Reverse VLOOKUP

There are 4 best methods of Excel reverse VLOOKUP: 

  1. Reverse VLOOKUP With VLOOKUP & CHOOSE function (one-dimensional lookup)
  2. Reverse VLOOKUP With INDEX MATCH function (either one-dimension lookup or two-dimensional lookup)
  3. Reverse VLOOKUP With OFFSET & MATCH function (two-dimensional lookup)

 

I. Excel REVERSE VLOOKUP: with VLOOKUP & CHOOSE function (One-Dimensional Lookup)

 

The CHOOSE function returns the specific value from a list of values supplied as arguments. We use this feature of the CHOOSE function as a table_array within the VLOOKUP function to perform Excel Reverse VLOOKUP.  

This formula only retrieves the value row-wise, which means one-dimensionally. 

SYNTAX:

Syntax of Excel REVERSE VLOOKUP: with VLOOKUP & CHOOSE function (One-Dimensional Lookup)

STEPS:

Steps of Excel REVERSE VLOOKUP: with VLOOKUP & CHOOSE function (One-Dimensional Lookup)
Figure 1: Steps of Excel REVERSE VLOOKUP: with VLOOKUP & CHOOSE function (One-Dimensional Lookup)

• =VLOOKUP($I3,CHOOSE ({1,2,3} , $C$3 : $C$12, $B$3 : $B$12 ,$G$3 :$G$12) ,2,0)   

We get the result: CA-1

 

• =VLOOKUP($I3,CHOOSE ({1,2,3} , $C$3 : $C$12, $B$3 : $B$12 ,$G$3 :$G$12) ,3,0)   

We get the result: $41,622

 

• Step 1

Select the cell to get the result of Reverse VLOOKUP (i.e., J3).

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

Syntax of the VLOOKUP function_1

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

Syntax of the VLOOKUP function

• Step 2

Select the lookup value locates in cell I3 and fix the Column address by pressing three times the F4 key. Thus the range converts from the relative to the mixed cell reference where it indicates the absolute column and relative row.

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

=VLOOKUP($I3,    

• Step 3:

In place of table_array, we use the CHOOSE function.

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

Syntax of the CHOOSE function_1

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

Syntax of the CHOOSE function

Complete the CHOOSE formula as follows: 

=VLOOKUP($I3, CHOOSE({1,2,3}, $C$3 : $C$12, $B$3 : $B$12, $G$3 : $G$12)

 

• 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,3}. 

Remember that according to the requirement, we can put 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 $C$3:$C$12. It is mandatory otherwise formula doesn’t work at all. Select the range and makes it absolute from relative cell reference by pressing a single time F4 key. 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 is placed on the left side of the lookup value i.e., $B$3:$B$12. Similarly, select the range and makes it absolute from relative cell reference by pressing a single time F4 key.

Remember that, it is optional and we can refer the column range on the right side of the lookup value column instead of the left. 

  • However, index number 3 refers to the column range is placed on the right side of the lookup value i.e., $G$3:$G$12. Similarly, select the range and makes it absolute from relative cell reference by pressing a single time F4 key.

Remember that, it is optional and we can refer the column range on the left side of the lookup value column instead of the right. 

 

Step 4:

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

reverse lookup or reverse vlookup

 

Thus, the phenomenon of VLOOKUP is called Reverse VLOOKUP or Reverse LOOKUP or Backward LOOKUP. In a word, we can say Vlookup to the left

Both way lookup

Both the VLOOKUP and CHOOSE functions to make a nested formula that retrieves the values both on the left and right sides of the lookup column. This circumstance is called both-way lookup or two-way 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.  

 

Step 6:

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

As a result, we get the result for Dialing Code ISO is CA-1 and Project Cost is $41,622

 

Step 7: Convert all the Formulas into Values

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

  • Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box; 
  • Or, Using the ‘Values‘ option in the ‘Paste Special’ dialog box.
Using the 'Values and number formats' option in the 'Paste Special' dialog box
Figure 2: Using the ‘Values and number formats’ option in the ‘Paste Special’ dialog box.

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

Figure: Using the 'Values' option in the 'Paste Special' dialog box.
Figure 3: Using the ‘Values’ option in the ‘Paste Special’ dialog box.

 


II. Excel REVERSE VLOOKUP: with the INDEX MATCH function

 

The INDEX MATCH function is the best alternative of the Excel Reverse Vlookup. However, both the functions make a nested formula that performs both-way lookup or two-way lookup – that means formula can perform on both the left and the right side of the lookup value column.

The MATCH function is used to return the position of the item, not the actual item. Whereas the INDEX function not only retrieves the cell content or item, but it also moves to any row or column.   

Thus, MATCH function is often used in conjunction with the INDEX function to obtain an item in the same row of a table, but from a different column. 

In Figure 4, the INDEX function is used with a nested MATCH function to obtain the ‘Dialing Code (ISO)’ and the ‘Project Cost’ for a specific ‘Dialing Code (UN)’.

While a single MATCH function is used within the INDEX function either as row_num or column_num and returns a value based on the single criterion (either row or column criterion), this type of lookup is called the one-dimensional lookup.  Similarly, while two MATCH functions are used within the INDEX function- one for the row_num and another for the column_num and returns a value based on the two criteria (both row and column criteria), this type of lookup in Excel is called the two-dimensional lookup or 2-D lookup.   

 

A. Excel REVERSE VLOOKUP: with the INDEX MATCH function (One-Dimensional LOOKUP)

 

SYNTAX:

Syntax of Excel REVERSE VLOOKUP: with the INDEX MATCH function (One-Dimensional LOOKUP)

 

STEPS:

An alternative of Excel REVERSE VLOOKUP: INDEX MATCH function in Excel
Figure 4: Excel Reverse VLOOKUP: by the INDEX MATCH function (one-dimensional lookup)

 

• =INDEX($B$3 : $B$12, MATCH($I3, $C$3 : $C$12, 0))

We get the result for the ‘Dialing code (ISO)’ = CA-1

 

• =INDEX($G$3 : $G$12, MATCH($I3, $C$3 : $C$12, 0))

We get the result for the ‘Project Cost’ = $41,622

If we observed both examples carefully, we find that only INDEX_array (answer area) changes in both cases. This is the key point for the INDEX MATCH one-dimensional lookup. 

 

• Step 1:

Select the cell to get the result of Reverse VLOOKUP (i.e., J3) with the help of the INDEX MATCH function.

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

Syntax of the INDEX function_1

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

Syntax of the INDEX function

The INDEX function always returns a value or item specified by the intersection of the row_number and column_number

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

row_num is the row number in the array (if omitted, column_num is required);

column_num is the column number in the array (if omitted, row_num is required);

area_num specifies which range from the reference argument to use. 

 

• If the array is one-dimensional, meaning it contains a single row or column, then both row_num and column_num are not required. 

For example, if we want to return the fourth item within a simple one-dimensional array using the following formula: 

=INDEX ({5,10,15,20,25,30}, 4)

Or, simply using the range =INDEX (A2:A6, 4)  

Which would return a 20, the fourth item in the array. 

 

• The INDEX function is more frequently used with two-dimensional arrays or tables, where both  row_num and column_num are required. 

For example, =INDEX (A2:D6, 4,2) 

The INDEX function retrieves the item fourth rows down and two columns over a range. That means the formula would retrieve the item from the cell B4.   

 

• If the array is two dimensional, which means it contains multiple columns and rows, and one of the arguments is omitted or set to zero, the INDEX function will return the whole row or column. 

For example, =SUM(INDEX (A2:D6, ,2))

As a result, the entire B column is totaled.  

 

Step 2: 

Select the array (answer range) inside the INDEX function.

(1) For the first instance, we are looking for a “Dialing Code (ISO)” and it is found in the range B3:B12. Fix the range (both column and row addresses) by pressing F4 once. Thus the range converts from the relative to the absolute cell reference. As a result, the range does not change when the formula is copied to another cell either horizontally or vertically. Thus the formula is written as: 

=INDEX ($B$3:$B$12,

(2) Similarly, for the second instance, we are looking for “Project Cost” and it is found in the range G3:G12. Make the range absolute by pressing F4 once. Thus we can write the formula as:

=INDEX ($G$3:$G$12,

 

Step 3: 

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

The Syntax of the MATCH function is as follows: 

Syntax of the MATCH function

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

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

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

    • A match_type of zero (0) finds the first item in the array that is an exact match with the lookup_value.
    • To find the item closest to but less than the lookup_value, use a match_type of -1
    • To find the item closest to but greater than the lookup_value, use a match_type of 1.  

In the last two cases, the values in the lookup_array must be in ascending order for the MATCH function to work correctly. 

The match_type is optional and will default to 1 if omitted from the arguments.  

 

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

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

Syntax of the MATCH function_01Then press the ‘Tab’ key, by default MATCH syntax appears within the INDEX function.

Syntax of the MATCH function

The complete formula as follows:   

=INDEX ($B$3:$B$12, MATCH($I3, $C$3:$C$12, 0)

=INDEX ($G$3:$G$12, MATCH($I3, $C$3:$C$12, 0)

$I3 = lookup_value reference to ‘CAN-1’ and fixed the column address by pressing three times F4 Key. As a result, the lookup_value is converted from the relative cell reference to the mixed column cell reference, where it indicates the absolute column and relative row address.

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

$C$3:$C$12 = lookup_value found in the range is called lookup_array  and fixed the range by pressing once the F4 key. Thus the range is converted to absolute from the relative cell reference.

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

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

 

• Step 4: 

Finally, press Enter. Formula ends by default and closes the last parenthesis as well. 

As a result, we get the result for Dialing Code ISO is CA-1 and Project Cost is $41,622

 

• Step 5: Convert all the Formulas into Values

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

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

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

02 Alternatives of Excel REVERSE VLOOKUP | VLOOKUP to the left_Using 'Values and number formats'
Figure 5: Using ‘Values and number formats’ in the Paste Special dialog box

 

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

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

02 Alternatives of Excel REVERSE VLOOKUP | VLOOKUP to the left_Using 'Values ' in the Paste Special dialog box
Figure 6: Using the ‘Values ‘ option in the Paste Special dialog box

 

 

B. Excel REVERSE VLOOKUP: with the INDEX MATCH function (Two-Dimensional LOOKUP/ 2-D LOOKUP)

 

If we use two MATCH functions within the INDEX function – one for the row_num and another for the column_num, then the formula returns the value based on the matches between the row criteria and the column criteria. This type of lookup is called the two-dimensional lookup or the 2-D lookup.  The two-dimensional lookup is an advanced lookup and it is the best alternative of Excel Reverse VLOOKUP or  VLOOKUP to the left

SYNTAX:

Syntax of Excel REVERSE VLOOKUP with the INDEX MATCH function (Two-Dimensional LOOKUP 2-D LOOKUP)

STEPS:

Steps of Excel REVERSE VLOOKUP with the INDEX MATCH function (Two-Dimensional LOOKUP 2-D LOOKUP)
Figure 7: Steps of Excel REVERSE VLOOKUP with the INDEX-MATCH function (Two-Dimensional LOOKUP 2-D LOOKUP)

 

  • =INDEX($A$2 : $G$12, MATCH($I3, $C$2 : $C$12, 0), MATCH(J$2, $A$2 : $G$2, 0) )

We get the result for the ‘Dialing code (ISO)’ = CA-1

 

  • =INDEX($A$2 : $G$12, MATCH($I3, $C$2 : $C$12, 0), MATCH(K$2, $A$2 : $G$2, 0) )

We get the result for the ‘Project Cost’ = $41,622

 

If we observed both examples carefully, we find the two key points for the INDEX MATCH two-dimensional lookup:

(i) All the ranges selected as an INDEX_array, i.e., A2:G12

(ii) While the formula copied to another cell, only lookup_value within the second MATCH function (i.e., column lookup value) changes accordingly, e.g., J2, K2

 

• Step 1:

Select the cell to get the result of Reverse VLOOKUP (i.e., J3) with the help of the INDEX MATCH function.

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

Syntax of the INDEX function_1

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

In the case of two-dimensional lookup (2-D lookup), The INDEX function always returns a value or item specified by the intersection of the row_number and column_number

The syntax of the INDEX function is as follows:

Syntax of the INDEX function

• Step 2:

Select the entire database or ranges as an array (answer range) inside the INDEX function, i.e., A2:G12.

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

=INDEX ($A$2:$G$12,

• Step 3:

In place of INDEX row_num, we should use the first MATCH function. The first MATCH function returns the position of the row number of an item.

The Syntax of the MATCH function is as follows: 

Syntax of the MATCH function

Just type a few letters “mat…” and select the MATCH function from the given suggestion list with the help of a down arrow (↓).  

Then press the ‘Tab’ key, by default MATCH syntax appears within the INDEX function.   

=INDEX ($A$2:$G$12, MATCH($I3, $C$2:$C$12, 0)

  • $I3 = lookup_value reference to ‘CAN-1’ and fixed the column address by pressing three times F4 Key. As a result, the lookup_value is converted from the relative cell reference to the mixed column cell reference, where it indicates the absolute column and relative row address.

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

  • $C$2:$C$12 = lookup_value found in the range (including the subject heading) and fixed the range by pressing a single time F4 key. Thus the range is converted to absolute from the relative cell reference.

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

  • 0  = for exact match

 

• Step 4:

In place of INDEX column_num, we should use the second MATCH function. The second MATCH function returns the position of the column number of an item.

Similarly, we type a few letters “mat…” and select the MATCH function from the given suggestion list with the help of a down arrow (↓), if required.  

Then press the ‘Tab’ key, the second MATCH syntax appears within the INDEX function. Complete the formula as shown below:   

=INDEX ($A$2:$G$12, MATCH($I3, $C$2:$C$12, 0), MATCH (J$2, $A$2 : $G$2, 0)

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

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

  • $A$2:$G$2 = lookup_value found in the range (including the subject heading) and fixed the range by pressing a single time F4 key. Thus the range is converted to absolute from the relative cell reference.

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

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

 

• Step 5: 

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

As a result, we get the result of Dialing Code ISO is CA-1 and Project Cost is $41,622

 

• Step 6: Convert all the Formulas into Values

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

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

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


III. Excel REVERSE VLOOKUP: with the OFFSET MATCH function (Two-Dimensional Lookup)

 

The OFFSET MATCH function is the best alternative of the Excel Reverse VLOOKUP or VLOOKUP to the left. However, both the functions make a nested formula that performs both-way lookup or two-way lookup – that means formula can perform on both the left and the right side of the lookup value column.

The OFFSET MATCH function returns the value based on a two-dimensional lookup

The OFFSET function returns a cell reference or a range that is a specified number of rows and columns from the reference cell. The OFFSET function works more dynamic with the combining of  INDEX() and MATCH() functions. 

The syntax of the OFFSET() function is: 

Syntax of OFFSET function

  • Reference: the starting point, basically the upper-left cell of a range is used as the reference. It is the required argument.   
  • Rows: the number of rows to move from the starting point; it can be positive (which indicates moving below the starting point) or negative (which indicates moving above the starting point). It is the required argument. 
  • Cols: the number of columns to move from the starting point; it can be positive (which indicates moving the right starting point) or negative (which indicates moving the left starting point). It is the required argument. 
  • Height: [Optional] the number must be positive and specifies the height of the returned range. If omitted, height is assumed to be identical to the reference argument. 
  • Width: [Optional] the number must be positive and specifies the width of the returned range. If omitted, width is assumed to be identical to the reference argument. 

 

We can use the OFFSET () function in place of the INDEX () function in most situations.  But we should remember two things: 

(i) We just select the upper-left cell (starting point) of the database or table rather than select the range or entire ranges.  

(ii) The OFFSET () function considered the starting point as zero (0) rather than 1. 

OFFSET Function_Basics
Figure 8: OFFSET Function_Basics

 

As we know that, the MATCH function is used to returned return the position of the item, not the actual item. Whereas the OFFSET function retrieves the cell content or item.   

Within the OFFSET() function two MATCH functions are used: one for the rows and another for the cols and return a value based on the two criteria (both row and column criteria), which means it works as two-dimensional lookup or 2-D lookup

How the OFFSET Function works
Figure 9: How the OFFSET Function works

 

After analysis of the above figure, we find that

(i) As per the MATCH() Function, the position of CAN-1 is 4 (row-wise), but according to the OFFSET() function the position is 3 because the OFFSET() function considered the starting point as zero (0) rather than 1.

(ii) However, as per the given row number or column number is supplied by the MATCH function, the OFFSET function retrieves the item from that row or column. Thus OFFSET function retrieves the item FRA-33 based on row number 4 is supplied by MATCH function.   

(iii) So we should always use -1 after the MATCH function to get the exact row number or column number for the OFFSET function. Therefore, the OFFSET function retrieves the exact item from row 3, i.e., CAN-1.    

SYNTAX:

Syntax-of-Excel-REVERSE-VLOOKUP-with-the-OFFSET-MATCH-function-(Two-Dimensional-Lookup)

STEPS:

Steps of Excel REVERSE VLOOKUP with the OFFSET MATCH function (Two-Dimensional Lookup)
Figure 10: Steps of Excel REVERSE VLOOKUP with the OFFSET MATCH function (Two-Dimensional Lookup)

 

  • =OFFSET($A$2 , MATCH($I3, $C$2 : $C$12, 0)-1, MATCH(J$2, $A$2 : $G$2, 0)-1 )

We get the result for the ‘Dialing code (ISO)’ = CA-1

 

  • =OFFSET($A$2 , MATCH($I3, $C$2 : $C$12, 0)-1, MATCH(K$2, $A$2 : $G$2, 0)-1 )

We get the result for the ‘Project Cost’ = $41,622

 

If we observed both examples carefully, we find the two key points for the OFFSET MATCH two-dimensional lookup:

(i) Unlike the INDEX function, only the upper-left cell (starting point) of a table or dataset is selected as a reference, i.e., A2.

(ii) While the formula copied to another cell, only lookup_value within the second MATCH function (i.e., column lookup value) changes accordingly, e.g., J2, K2

 

• Step 1:

Select the cell to get the result of Excel Reverse VLOOKUP (i.e., J3) with the help of the OFFSET MATCH function.

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

OFFSET Syntax_1

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

Syntax of OFFSET function

In the case of two-dimensional lookup (2-D lookup), The OFFSET function always returns a value or item specified by the intersection of the row_number and column_number

 

• Step 2:

Unlike the INDEX function, only the upper-left cell (starting point) of a table or dataset is selected as a reference, i.e., A2. 

Fix the cell (both column and row addresses) by pressing F4 once. Thus the cell is converted from the relative to the absolute cell reference. As a result, the cell remains fixed/unchanged when the formula is copied to another cell either horizontally or vertically. Thus the formula is written as: 

=OFFSET ($A$2,

• Step 3:

In place of OFFSET rows, we should use the first MATCH function. The first MATCH function returns the position of the row number of an item.

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

Syntax of the MATCH function_01

Then press the ‘Tab’ key, by default MATCH syntax appears within the OFFSET function.   

Syntax of the MATCH function

Complete the formula as shown below:

=OFFSET ($A$2, MATCH($I3, $C$2:$C$12, 0)-1

  • $I3 = lookup_value reference to ‘CAN-1’ and fixed the column address by pressing three times F4 Key. As a result, the lookup_value is converted from the relative cell reference to the mixed column cell reference, where it indicates the absolute column and relative row.

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

  • $C$2:$C$12 = lookup_value found in the range (including the subject heading) and fixed the range by pressing a single time F4 key. Thus the range is converted to absolute from the relative cell reference.

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

  • 0  = for exact match
  • -1 = subtract with the row number retrieved by the MATCH function, as a result, we get the exact row number for the OFFSET function 

 

• Step 4:

In place of OFFSET cols, we should use the second MATCH function. The second MATCH function returns the position of the column number of an item.

Similarly, we type a few letters “mat…” and select the MATCH function from the given suggestion list with the help of a down arrow (↓), if required.  

Then press the ‘Tab’ key, the second MATCH syntax appears within the INDEX function. Complete the formula as shown below:  

=OFFSET ($A$2, MATCH($I3, $C$2 : $C$12, 0)-1, MATCH (J$2, $A$2 : $G$2, 0)-1

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

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:$G$2 = lookup_value found in the range (including the subject heading) and fixed the range by pressing a single time F4 key. Thus the range is converted to absolute from the relative cell reference.

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

  • 0  = for exact match
  • -1 = subtract with the column number retrieved by the MATCH function, as a result, we get the exact column number for the OFFSET function 

 

• Step 5: 

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

As a result, we get the result of ‘Dialing Code (ISO)’ is CA-1 and ‘Project Cost’ is $41,622

 

• Step 6: Convert all the Formulas into Values

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

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

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


Read-More_4

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
Advance Excel Forum

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

Editor's Rating:
5

Leave a Comment

You may also like