Buzz
Vlookup is an indispensable function in Excel, allowing quick retrieval of corresponding values from the left column. However, when it comes to searching values in any other column and returning relative matches from the left, standard Vlookup falls short. This article guides you on how to use Reverse Vlookup effectively. Dive in!
USING VLOOKUP TO FETCH DATA FROM RIGHT TO LEFT WITH VLOOKUP AND IF
To fetch data from the right column to the left column, you can use the IF function combined with the Vlookup function. The formula below will help you.
For example, suppose you have a data table. Now you have the Code, and now you want to fetch data into the Name column corresponding to the Code.
Select the cell in the Name column and enter the formula as follows in the fx bar: =VLOOKUP(H7,IF({1,0},$C$7:$C$11,$D$7:$D$11),2,0) then press Enter. The data will be entered accordingly.
You can drag from the cell just completed in the Name column down to let the system perform automatically with the remaining cells.
Enter the following formula into the fx bar: =INDEX($D$7:$D$11,MATCH(H7,$C$7:$C$11,0)) , then click Enter. The resulting data will be displayed as shown below.
Next, drag from the starting cell to the end to copy the formula for the entire column.
Note:
- In the formula above, H7 represents the data we want to retrieve to return the corresponding result, D7:D11 is the list containing the values you want to return, and C6:C11 is the column of data we want to search.
UTILIZING VLOOKUP TO FETCH DATA FROM RIGHT TO LEFT WITH VLOOKUP AND CHOOSE FUNCTION
The formula above utilizing the INDEX and MATCH functions does not use the VLOOKUP function, and as you may find, the INDEX and MATCH functions can be somewhat challenging to remember. In such cases, using the CHOOSE function in combination with VLOOKUP offers an alternative solution.
Enter the following formula into the fx bar: =VLOOKUP(H7,CHOOSE({2,1},$D$7:$D$11,$C$7:$C$11),2,0) , then click Enter. The resulting data will be displayed as shown below.
Next, drag down to complete the data retrieval process for the cells below.
Note:
- In the formula above, H7 represents the value we want to match to retrieve corresponding data, C7:C10 represents the column we want to search, and D7:D10 represents the column of data we want to retrieve.
Here is a guide on how to use the Vlookup function from right to left. Hopefully, through this article, you will no longer encounter difficulties in using the Vlookup function to retrieve data from right to left. If you encounter any difficulties during the process, don't hesitate to comment, the technical team at Mytour will support you
