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.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...