May 12 2020 01:44 PM
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?
May 13 2020 04:15 AM
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)