vlookup的近似匹配规则

https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

1. Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don't specify anything, the default value will always be TRUE or approximate match.

 

How To Use Vlookup Exact And Approximate Match In Excel?

Use Vlookup Function To Get The Approximate Matches In Excel

Sometimes, your specified data is not in the data range, to get the nearest match with the given data, you need to use the vlookup to get an approximate match.

If you have the following range data, the specified quantity number 58 is not in the Quantity column, how to get its closest unit price in column B?

Please enter the below formula into a blank cell:

=VLOOKUP(D2,$A$2:$B$10,2,TRUE)

Then, drag the fill handle down to the cells to apply this formula, and you will get the approximate matches based on the given values, see screenshot:

Notes:

1. In the above formula: D2 is the value which you want to return its relative information, A2:B10 is the data range you use, the number 2 indicates the column number that your matched value is returned and the TRUE refers to the approximate match.

2. The approximate match returns the next largest value that is less than your specific lookup value.  可以理解为向上取。58在50和100之间，50在上面，所以取50所在行的列。

3. To use the vlookup function to get an approximate match value, your first column in the table must be sorted in ascending order, otherwise it will return a wrong result.

 

作者:Chuck Lu    GitHub