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:
➢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 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:
• 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:
• 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.
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:
• =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.
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 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.
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.
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.