- To post as a guest, your comment is unpublished.· 3 months agoHello, 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!
- To post as a guest, your comment is unpublished.· 3 months agoAnd 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.
- To post as a guest, your comment is unpublished.· 3 months agoand 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
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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 months agoFor Reverse Vlookup function to find the last matching value with formula, is this possible between 2 sheets? Thank you