03 Best Ways Excel REVERSE VLOOKUP- VLOOKUP to the left

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


Excel Reverse VLOOKUP – is used for VLOOKUP to the left and is a useful formula in data analysis and big data handling.     

The VLOOKUP function searches value only to the right, but the INDEX MATCH or VLOOKUP and 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 the VLOOKUP (with the CHOOSE function) is called Reverse VLOOKUP

In this tutorial, we discussed 3 alternative methods of Excel reverse VLOOKUP mentioned below: 

(01) Reverse VLOOKUP With the VLOOKUP & CHOOSE function (one-dimensional lookup)

(02) Reverse VLOOKUP With the INDEX MATCH function (either one-dimension lookup or two-dimensional lookup)

(03) Reverse VLOOKUP With the OFFSET & MATCH function (two-dimensional lookup)

(01). EXCEL REVERSE VLOOKUP: WITH THE VLOOKUP & CHOOSE FUNCTION

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

The VLOOKUP and CHOOSE nested formula performs VLOOKUP to the left that means the formula retrieves the value from the left of the lookup_column. 

➢ SYNTAX:

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

➢STEPS TO START:

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

• Step 1: Select the cell where to start the VLOOKUP formula and get the result of the Reverse VLOOKUP (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. 

Syntax of the VLOOKUP function_1

Promptly press the Tab key which will select the VLOOKUP function and the VLOOKUP syntax appears with an open parenthesis. 

Syntax of the VLOOKUP function

• Step 2: Select the lookup_valuethe first argument of the VLOOKUP function, locates in cell I3, and fix the Column address by pressing the F4 key thrice. It looks like $I3. So the cell is converted 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 addresses do not change at all but the row addresses change accordingly.

=VLOOKUP($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 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.

Syntax of the CHOOSE function_1

Then press the Tab key which will select the CHOOSE function and the CHOOSE syntax appears with the open parenthesis. We need to fill up all the required arguments in the CHOOSE function.

Syntax of the CHOOSE function

After applying the CHOOSE formula it looks like this:

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

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

As per our requirement, we can put 4, 5, 6… so on till 254.

➢ Index number 1 always refers to the lookup column range, i.e., C3:C12. It is mandatory otherwise formula doesn’t work at all. Select the range and makes it absolute from the relative cell reference by pressing the F4 key once. The range looks like $C$3:$C$12. 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., B3:B12. Similarly, select the range and makes it absolute from the relative cell reference by pressing the F4 key once. The range looks like $B$3:$B$12. As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.

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

➢ Index number 3 refers to the column range is placed on the right side of the lookup value i.e., G3:G12. Similarly, select the range and makes it absolute from the relative cell reference by pressing the F4 key once. The range looks like $G$3:$G$12. As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.

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

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

Performing Reverse VLOOKUP / VLOOKUP to the LEFT/ VLOOKUP Backwards

reverse lookup or reverse vlookup

Performing RIGHT VLOOKUP / VLOOKUP to the RIGHT

Both way lookup

The VLOOKUP and CHOOSE function combinedly form a nested formula that can retrieve the value from both on the left and right sides of the lookup column. This phenomenon is called the both-way lookup or two-way lookup.  

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. 

• Step 6: 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($I3, CHOOSE({1,2,3}, $C$3:$C$12, $B$3:$B$12, $G$3:$G$12), 2, 0)

➪ The formula returns the result: CA-1

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

➪ The formula returns the result: $41,622

• Step 7: 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 the source file.  

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

➢ Method 1: Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box by the Excel shortcut Alt+E+S+U (sequentially press Alt, E, S, U) / Alt+Ctrl+V+U (press Alt+Ctrl+V, E, S, then U) press Enter or click on OK.

EXCEL REVERSE VLOOKUP WITH VLOOKUP & CHOOSE FUNCTION_Convert All the 'Formulas' into 'Values'_2

➢ Method 2: Alternatively, Using the ‘Values‘ option in the ‘Paste Special’ dialog box by the Excel shortcut Alt+E+S+V (sequentially press Alt, E, S, V) / Alt+Ctrl+V+V (press Alt+Ctrl+V, E, S, then V) press Enter or click on OK.

EXCEL REVERSE VLOOKUP WITH VLOOKUP & CHOOSE FUNCTION_Convert All the 'Formulas' into 'Values'_1

(02). EXCEL REVERSE VLOOKUP: WITH THE INDEX MATCH FUNCTION

The INDEX MATCH function is the best alternative to the Excel Reverse Vlookup. However, both the functions make a nested formula that performs both-way lookup or two-way lookup – that means the 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, the 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 2D lookup.   

■ EXAMPLE 1: 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 TO START:

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

• 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 the F4 key 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 the F4 key 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. 

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

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 values either in two ways:  

➢ Method 1: Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box:

Copy the range of cells (i.e., J3:K3) with a formula using the Excel shortcut Ctrl+C  ➪ Press Alt+E+S+U (sequentially press Alt, E, S, U) or Alt+Ctrl+V+U (press Alt+Ctrl+V, E, S, then U) which will select the ‘Values and number formats‘ option in the Paste Special dialog box ➪ Press Enter or click 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

➢ Method 2: Using the ‘Values‘ option in the ‘Paste Special’ dialog box: 

Copy the range of cells (i.e., J3:K3) with formula by using the Excel shortcut Ctrl+C ➪ Press Alt+E+S+V (sequentially press Alt, E, S, V) or Alt+Ctrl+V+V (press Alt+Ctrl+V, E, S, then V) which will select the ‘Values‘ option in the Paste Special dialog box ➪ Press Enter or click 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

■ EXAMPLE 2: EXCEL REVERSE VLOOKUP WITH THE INDEX MATCH FUNCTION (TWO DIMENSIONAL LOOKUP / 2D 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 2D 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 TO START:

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 or 2D 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 a two-dimensional lookup (2D 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 dataset or ranges as an array (answer range) in the INDEX function, i.e., A2:G12.

Fix the range (both column and row addresses) by pressing the F4 key 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 the F4 Key thrice. 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 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 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 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:$G$2 = lookup_value found in the range (including the subject heading) 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.

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

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

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 either in two ways: 

➢ Method 1: Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box:

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

➢ Method 2: Using the ‘Values‘ option in the ‘Paste Special’ dialog box: 

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

(03). EXCEL REVERSE VLOOKUP: WITH THE OFFSET MATCH FUNCTION (2D 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 (2D 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 for 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 dataset 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, the MATCH function is used to 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 2D 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 TO START:

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, the OFFSET syntax appears with the open parenthesis: 

Syntax of OFFSET function

In the case of two-dimensional lookup (2D 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 the F4 key 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 the OFFSET rows argument, 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 the F4 key thrice. 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 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 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 the OFFSET cols argument, we will 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 theTab 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 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.

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

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

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 either in two ways: 

➢ Method 1: Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box:

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

➢ Method 2: Using the ‘Values‘ option in the ‘Paste Special’ dialog box: 

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

If you would like to improve your academic and professional career as well, then the below courses help you a lot. Instead of Advanced Excel, a number of best courses suggested you through this platform that boosts your confidence and flies your career high.
AEF_192by192
Advance Excel Forum

Premium Courses on ed2go

intermediate excel courses 20134&bids=739114

Intermediate Microsoft Excel 2013

Bestseller
4.9/5
accounting with excel 2019 suite4&bids=739114

Accounting with MS Excel 2019 Suite

Bestseller
4.9/5
excel classes4&bids=739114

Microsoft Excel 2016 Series

Bestseller
4.9/5
excel 2019 series4&bids=739114

Microsoft Excel 2019/Office 365 Series

Bestseller
4.9/5
Microsoft Excel Access 194&bids=739114

Microsoft Excel and Access 2019 Suite

Bestseller
4.9/5
microsoft excel 2016 advanced training4&bids=739114

Advanced Microsoft Excel 2016

Bestseller
4.9/5

Premium Courses on Coursera

Course 2 logo crop4&bids=759505

Data Analysis and Visualization

Bestseller
4.9/5
04 Fundamentals4&bids=759505

Excel Fundamentals for Data Analysis

Bestseller
4.9/5
04 Visualisation4&bids=759505

Data Visualization in Excel

Bestseller
4.9/5
044&bids=759505

Excel Skills for Business

Bestseller
4.9/5
Specialization Certificate Emblem AADS Final4&bids=759505

Advanced Data Science with IBM

Bestseller
4.9/5
118478 iconImage EducationCoursera VA orange4&bids=759505

Data Analysis and Reporting in SAS Visual Analytics

Bestseller
4.9/5
4&bids=759505

Introduction to Spreadsheets and Models

Bestseller
4.9/5
4&bids=759505

Accounting Analytics

Bestseller
4.9/5
Introduction to Data Engineering Image4&bids=759505

Introduction to Data Engineering

Bestseller
4.9/5

Premium Courses on Udemy

1974808 0835 34&bids=507388

Master Excel Functions in Office 365 – Excel Dynamic Arrays (Learn to Use Excel’s NEW Functions (FILTER, UNIQUE, SORT, XLOOKUP.) to Dramatically Simplify the Work You Do in Excel.)

High Rated
4.9/5
2542943 541c 64&bids=507388

Excel Essentials for the Real World (Complete Excel Course) [Microsoft Excel Beginner to Professional. Includes Pivot Tables, Power Query, NEW Formulas. (Excel 365, 2019 & 2016)]

Bestseller
4.8/5
773214 f3b8 94&bids=507388

Advanced Excel – Top Excel Tips & Formulas (Master Advanced Excel Formulas. Solve Complex Problems. Learn Advanced Excel Skills to Save Time & Impress (Excel 2010)

Bestseller
4.6/5
164058 e914 24&bids=507388

The Ultimate Excel Programmer Course (Learn Excel VBA from Scratch with Dan Strong, Bestselling Excel Expert with Over 180K Students Worldwide!)

Bestseller
4.8/5
3614594 b3c5 34&bids=507388

Microsoft Excel Certification Exam Prep: MO-201 Excel Expert (Ace the Excel MO-201 Exam. Learn advanced data analysis & earn the Excel Expert Certification (MS Excel 2019/Office 365)

Bestseller
4.8/5
575434 0e4a 54&bids=507388

Excel Charts, Graphs & Data Visualization in Excel (Master 20+ Advanced Dynamic Excel Charts and Create Impressive Excel Graphs & Data Visualization in Microsoft Excel)

High Rated
4.8/5
Read-More-5

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.


Like it? Share with your friends!

Share via

Join Our Community List

Community grow with You. * VERIFY & CONFIRM YOUR EMAIL *

Thanks for your interest joining to Advance Excel Forum community.

Fill the Correct Information.

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

We use cookies to ensure that we give you the best experience on our website (by analyzing).  Please see our cookies page for further details or agree by clicking the 'Accept' button.

 

Cookie settings

Below you can choose which kind of cookies you allow on this website. Click on the "Save cookie settings" button to apply your choice.

FunctionalOur website uses functional cookies. These cookies are necessary to let our website work.

AnalyticalOur website uses analytical cookies to make it possible to analyze our website and optimize for the purpose of a.o. the usability.

Social mediaOur website places social media cookies to show you 3rd party content like YouTube and FaceBook. These cookies may track your personal data.

AdvertisingOur website places advertising cookies to show you 3rd party advertisements based on your interests. These cookies may track your personal data.

OtherOur website places 3rd party cookies from other 3rd party services which aren't Analytical, Social media or Advertising.