Skip to main content

How to reverse vlookup function in Google sheet?

Author: Xiaoyang Last Modified: 2017-10-30

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?

Reverse Vlookup function to find the last matching value with formula

Reverse Vlookup function to find the value from right to left column with formula


Reverse Vlookup function to find the last matching value with formula

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.


Reverse Vlookup function to find the value from right to left column with formula

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

ūü§Ė Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions‚Ķ
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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

Description


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!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How if we want to find the last non-empty matching value using this formula?
This comment was minimized by the moderator on the site
Hello, Ardi,To solve your problem, please apply the below formula:=ArrayFormula(MAX(IF($A$2:$A$13=D2,$B$2:$B$13,)))
Please try, hope it can help you!
This comment was minimized by the moderator on the site
For Reverse Vlookup function to find the last matching value with formula, is this possible between 2 sheets? Thank you
This comment was minimized by the moderator on the site
Hello, Rox,



To vlookup the last matching item between two sheets in Google sheets, please apply the following formula:

Note: Sheet3 is the sheet contains the original table data, and A2 is the cell in your current sheet that you want to get the result, please change them to your need.

=ArrayFormula(INDEX(Sheet3!A:B, MAX(filter(ROW(Sheet3!A:A), Sheet3!A:A=A2)),2))



Please try, hope it can help you!
This comment was minimized by the moderator on the site
bro it doesnt work...
This comment was minimized by the moderator on the site
Hello Georgius,Sorry to hear that. But I found an amazing formula which can really work. I noticed that only using Vlookup formula can't solve the problem. We need to combine the LOOKUP function with SORT function. 
Please see the two screenshots. In Sheet1, range B1:C8 is the original table data. In Sheet2, cell B2 (Apple) is the lookup value. Please copy the formula in a blank cell to get the result: =LOOKUP(B2, SORT(Sheet1!B1:B8), SORT(Sheet1!C1:C8,Sheet1!B1:B8,TRUE))It returns 88, the last matching value of B2 (Apple). Please have a try. Thanks!
Sincerely,Mandy
This comment was minimized by the moderator on the site
And is it possible to get the matching value before the last matching value? For ex., you have 200 matching values. Can you instantly get the 199th matching value? Thank you.
This comment was minimized by the moderator on the site
and is it possible to get the matching value before the last matching value? Ex. if you have 200 matching values. can you instantly get the 199th values? Thank you
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations