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

**are more flexible than VLOOKUP. While reverse lookup is conducted by the VLOOKUP (with the CHOOSE function) is called**

__VLOOKUP and CHOOSE__**.**

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

TABLE OF CONTENTS:

Toggle**(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:**

**➢STEPS TO START:**

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

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

• Step 2: Select the *lookup_value, *the 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.

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.

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

➢ Performing RIGHT VLOOKUP / VLOOKUP to the RIGHT

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

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

**(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:**

➢ STEPS TO START:

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

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

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

*• array *or

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

*reference**• 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:

*•*** 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. Then press the ‘**Tab’** key, by default MATCH syntax appears within the INDEX 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.

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

**■ 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:**

➢ STEPS TO START:

**• **=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 (↓).

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:

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

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:

**• **Referenc**e****:** 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.

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

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

➢ STEPS TO START:

**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 (↓).

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

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.

Then press the **‘Tab’** key, by default MATCH syntax appears within the OFFSET 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.

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

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

## Premium Courses on ed2go

## Premium Courses on Coursera

## Premium Courses on Udemy

**12 Examples || How to Use Excel Go To Special?**

**07 Points Guided You How to Find And Replace in Excel?**

**05 Points Should Learn How to Freeze Panes in Excel?**

**12 Things Guided You How to Manage An Excel Workbook**

**03 Useful Methods : Add Numbers With AutoSum Excel**

**04 Simple to Advanced Methods: How to Filter in Excel?**

**05 Best Ways: Create Password Protect Excel & Unprotect it**

**08 Best Examples: How to Use Excel Conditional Formatting?**

**04 BEST WAYS: HOW TO TRANSPOSE DATA IN EXCEL**

**Tutorial 01: How to Use Excel SUMIFS Function with Single & Multiple Criteria**

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.

* VERIFY & CONFIRM YOUR EMAIL * We respect your privacy and take protecting it seriously*.