SOLVED

Question related to Finding Nearest value

%3CLINGO-SUB%20id%3D%22lingo-sub-2719713%22%20slang%3D%22en-US%22%3EQuestion%20related%20to%20Finding%20Nearest%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2719713%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(4819).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308135iF7C3DBE4427B05C2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(4819).png%22%20alt%3D%22Screenshot%20(4819).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20above%20screenshot%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20value%20of%20%3CSTRONG%3E7.689%3C%2FSTRONG%3E%20%26amp%3B%20Now%20I%20want%20to%20%3CSTRONG%3Efind%20two%20closest%20values%3C%2FSTRONG%3E%20from%20the%20table%20given%20(one%20must%20be%20slight%20higher%20%26amp%3B%20one%20must%20be%20slight%20lower%20than%20the%20original%20value%20i%20have)%2C%20the%20values%20must%20be%20%3CSTRONG%3E7.838%20(%20slight%20higher%20value)%20%26amp%3B%207.536%20(%20slight%20lower%20value)%3C%2FSTRONG%3E%2C%20how%20do%20i%20find%20those%20values%20from%20the%20list%20have%20with%20the%20help%20of%20formula%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help..%3F%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EHere%20is%20a%20attached%20file..%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2719713%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-2719754%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Finding%20Nearest%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2719754%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DXLOOKUP(E5%2CB3%3AB15%2CB3%3AB15%2C%2C1)%0A%3DXLOOKUP(E5%2CB3%3AB15%2CB3%3AB15%2C%2C-1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2719764%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Finding%20Nearest%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2719764%22%20slang%3D%22en-US%22%3ESir%2C%20i%20am%20using%20Microsoft%20Office%202019%20Home%20and%20Student.%3CBR%20%2F%3ESo%2C%20XLOOKUP%20function%20is%20not%20there.%3CBR%20%2F%3EAny%20alternative%20solution%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2719836%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Finding%20Nearest%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2719836%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DAGGREGATE(14%2C%206%2C%201%2F(%24B%243%3A%24B%2415%26lt%3B%3D%24E%245)*%24B%243%3A%24B%2415%2C1)%0A%3DAGGREGATE(15%2C%206%2C%201%2F(%24B%243%3A%24B%2415%26gt%3B%3D%24E%245)*%24B%243%3A%24B%2415%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hello Everyone,

Screenshot (4819).png

 

In above screenshot, 

I have value of 7.689 & Now I want to find two closest values from the table given (one must be slight higher & one must be slight lower than the original value i have), the values must be 7.838 ( slight higher value) & 7.536 ( slight lower value), how do i find those values from the list have with the help of formula?

 

I am using Microsoft Office 2019 Home and Student.

 

Please help..???

Here is a attached file...

5 Replies

@Zan_Hanifee 

=XLOOKUP(E5,B3:B15,B3:B15,,1)
=XLOOKUP(E5,B3:B15,B3:B15,,-1)

 

Sir, i am using Microsoft Office 2019 Home and Student.
So, XLOOKUP function is not there.
Any alternative solution?
best response confirmed by Zan_Hanifee (Regular Contributor)
Solution

@Zan_Hanifee 

As variant

=AGGREGATE(14, 6, 1/($B$3:$B$15<=$E$5)*$B$3:$B$15,1)
=AGGREGATE(15, 6, 1/($B$3:$B$15>=$E$5)*$B$3:$B$15,1)
Thank you so much sir

@Zan_Hanifee , you are welcome