How to reverse vlookup function in Google sheet?
In Google sheet, we can apply the Vlookup function to find and return the first and left matching value based on a given criterion. But, sometimes, you need to reverse the Vlookup function to get the last matching value or the value from the left column. How could you deal with this job in Google sheet?
To vlookup and return the last matching value based on a criterion, please apply the following formula:
Enter this formula: =INDEX(A:B, MAX(filter(ROW(A:A), A:A=D2)),2) into a blank cell where you want to return the result, and then press Enter key, the last matching value is displayed at once, see screenshot:
Note: In the above formula, A:B is the data range that you want to vlookup from, A:A is the column data which contains the criterion you want to vlookup, and D2 is the criterion that you want to return the matching value based on, 2 is the column number you want to return the value from.
Sometimes, you need to vlookup from right to left, the normal vlookup function will not work, here is a simple formula can help you, please do as this:
Please enter this formula: =index(A1:A16,match(D2,B1:B16,0)) into a blank cell where you want to put the result, and then press Enter key, the matching value from the left column will be returned as following screenshot shown:
Note: In the above formula: A1:A16 is the column range contains the matching value you want to return, D2 is the cell with criterion and B1:B16 is the range which contains the criterion you want to vlookup from.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!