I need clarification on using the fourth parameter in the VLOOKUP function. I understand it is meant for an exact or close match.

How does Excel derive the closest match?

For example, My range has values: 1111 1234 1236 2222

If the value I am comparing is 1235. Will I get the closest match as 1234 or 1236, and why?

CodePudding user response：

It will return 1234 as that was the last number lower or equal to the input.

Note that TRUE requires that the lookup values be sorted ascending otherwise it will return the wrong value as it will stop as soon as it finds where the value goes from lower to higher.

For example if your data was in this order: {1111,2222,1234,1236} your search of 1235 would return 1111

One more note, XLOOKUP does not have the same restriction as you can decide whether you want the exact or next smaller or exact or next larger. Which means it can be sorted how ever and it will return either 1234 or 1236 depending on the choice.

Here the -1 tells the XLOOKUP I want the Exact or next smaller.