How to vlookup next largest value in Excel?
Have you ever noticed that when you apply the VLOOKUP function to extract the approximate matched value by using this formula: =VLOOKUP(D2,A2:B8,2,TRUE), you will get the value less than the lookup value. However, in certain situations, you might need to find the next largest record. Look at the following example, I want to look up the corresponding date of quantity 954, now, I want it to return the date which has the higher value than 954 in the VLOOKUP, so Oct rather than Sep. In this article, I can talk about how to lookup next largest value and return its relative data in Excel.
Vlookup and return value in a list:
With this formula of Kutools for Excel, you can quickly vlookup the exact value from a list without any formulas.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
To deal with this task, the following formulas may help you, please do as follows:
1. Please enter this array formula: =INDEX(B2:B13,MATCH(TRUE,A2:A13>D5,0)) into a blank cell where you want to put the result, see screenshot:
2. Then press Ctrl + Shift + Enter keys together to get the correct result you need as follows:
1. Excepting the above array formula, here also is another normal formula: =INDEX(B2:B13,MATCH(D5,A2:A13)+(LOOKUP(D5,A2:A13)<>D5)), after typing this formula and just press Enter key to find the next largest value and return its corresponding value.
2. In the above formulas, B2:B13 is the data range that you want to extract the matched value, A2:A13 is the data range including the criterion that you want to lookup from , and D5 indicates the criteria you want to get its relative value of adjacent column, you can change them to your need.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years ago=INDEX(B2:B13,MATCH(D5,A2:A13)+(LOOKUP(D5,A2:A13)<>D5)) not working
- To post as a guest, your comment is unpublished.· 4 years agoThanks for this demo. It helped me out, and I can't believe Excel doesn't have an easier way to do this. The only thing I had to change was to add an "=" to : =INDEX(B2:B13,MATCH(TRUE,A2:A13>=D5,0))