Selecting the closest value when using the function XlOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-1384107%22%20slang%3D%22en-US%22%3ESelecting%20the%20closest%20value%20when%20using%20the%20function%20XlOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1384107%22%20slang%3D%22en-US%22%3E%3CP%3Ein%20first%20table%20there%20are%20numbers%3A%2014.7%2013.9%2017.2%3C%2FP%3E%3CP%3EFor%20numbers%20from%20the%20first%20table%20need%20to%20find%20the%20closest%20values%20from%20the%20other%20table%20where%20the%20numbers%20are%2015%2014%2017%3C%2FP%3E%3CP%3EIn%20the%20XLOOKUP%20functions%2C%20you%20can%20specify%20that%20if%20you%20don't%20find%20the%20exact%20value%2C%20take%20the%20previous%20or%20next%20one%2C%20but%20you%20can't%20specify%20that%20if%20you%20don't%20find%20the%20exact%20value%2C%20take%20the%20nearest%20one%3C%2FP%3E%3CP%3Efor%20example%2C%20by%2014.7%20the%20nearest%20is%2015%20(next)%20and%20by%2017.2%20the%20nearest%20will%20be%2017%20(previous)%3C%2FP%3E%3CP%3EThe%20question%20is%2C%20can%20I%20specify%20%22take%20the%20nearest%20value%22%20instead%20of%20the%20next%20or%20previous%20one%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1384107%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386012%22%20slang%3D%22en-US%22%3ERe%3A%20Selecting%20the%20closest%20value%20when%20using%20the%20function%20XlOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665329%22%20target%3D%22_blank%22%3E%40VladislavMyUsernam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20try%20simple%20LOOKUP()%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20435px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F191295i3EDD83D8C27E87F6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eas%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLOOKUP(2%2C1%2F(MIN(ABS(%24B%243%3A%24B%245-E3))%3DABS(%24B%243%3A%24B%245-E3))%2C%24C%243%3A%24C%245)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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
Highlighted

@VladislavMyUsernam 

You may try simple LOOKUP()

image.png

as

=LOOKUP(2,1/(MIN(ABS($B$3:$B$5-E3))=ABS($B$3:$B$5-E3)),$C$3:$C$5)