Forum Discussion
Selecting the closest value when using the function XlOOKUP
in first table there are numbers: 14.7 13.9 17.2
For numbers from the first table need to find the closest values from the other table where the numbers are 15 14 17
In the XLOOKUP functions, you can specify that if you don't find the exact value, take the previous or next one, but you can't specify that if you don't find the exact value, take the nearest one
for example, by 14.7 the nearest is 15 (next) and by 17.2 the nearest will be 17 (previous)
The question is, can I specify "take the nearest value" instead of the next or previous one?
1 Reply
You may try simple LOOKUP()
as
=LOOKUP(2,1/(MIN(ABS($B$3:$B$5-E3))=ABS($B$3:$B$5-E3)),$C$3:$C$5)
