Excel Study · Module 6: Lookups
Class 48VLOOKUP: approximate match
Intermediate2:20 videoFree
With TRUE as its last argument, VLOOKUP finds the band a value falls in: the largest band start that is not more than the value.
Try this AI prompt
Works in ChatGPT, Gemini, Copilot or Claude.
In Excel, F2:G5 is a commission rate table: sales thresholds in column F, from smallest to largest, and rates in column G. B3 has a salesperson's sales. Using VLOOKUP, write a formula for D7 that gives the commission: the sales times the rate for their band.
The answer that works
✓ checked in Excel=B3*VLOOKUP(B3,F2:G5,2,TRUE)
AI can get Excel wrong. Paste its answer into Excel and test it before you trust it.