Suggestion for Xlookup fuction - add two more [match mode] next smaller item, next larger item

%3CLINGO-SUB%20id%3D%22lingo-sub-2393204%22%20slang%3D%22en-US%22%3ESuggestion%20for%20Xlookup%20fuction%20-%20add%20two%20more%20%5Bmatch%20mode%5D%20next%20smaller%20item%2C%20next%20larger%20item%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393204%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3ECurrently%2C%20there%20are%204%20match%20modes%20available%20in%20the%20function%20XLOOKUP%3B%200%2C%20-1%2C%201%2C%202.%3C%2FP%3E%3CP%3EI%20suggest%20two%20more%20match%20modes%20namely%20-3%2C%203.%3C%2FP%3E%3CP%3E-3%20is%20for%20the%20next%20smaller%20item.%20(exact%20match%20is%20skipped%20here.)%3C%2FP%3E%3CP%3E3%20is%20for%20the%20next%20larger%20item.%20(exact%20match%20is%20skipped%20here.)%3C%2FP%3E%3CP%3ESometimes%20you%20don't%20need%20%22exact%20match%20or%20next%20smaller%20item.%22%3C%2FP%3E%3CP%3ESometimes%20you%20don't%20need%20%22exact%20match%20or%20next%20larger%20item.%22%3C%2FP%3E%3CP%3EYou%20just%20need%20the%20%22next%20smaller%20item%22%20or%20%22next%20larger%20item%22%20to%20search%20from%20an%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20the%20solutions%20is%20-%3C%2FP%3E%3CP%3Eto%20add%20%2B%200.0001%20to%20the%20lookup%20value%20for%20the%20next%20larger%20item.%3C%2FP%3E%3CP%3Eto%20subtract%20-%200.0001%20from%20the%20lookup%20value%20for%20the%20next%20smaller%20item.%3C%2FP%3E%3CP%3E(We%20have%20to%20put%20as%20many%200s%20after%20the%20decimal%20as%20per%20the%20data.%20If%20the%20data%20in%20the%20array%20has%204%20or%20more%200s%20after%20decimal%20which%20is%20not%20likely%20though%2C%20we%20have%20to%20take%20care%20of%20that.%20In%20that%20scenario%20we%20have%20to%20add%20or%20subtract%200.00001)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20in%20order%20to%20keep%20the%20formula%20clean%2C%20the%20below%20thing%20can%20be%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20the%20suggestion%20is%20incorporated%20in%20excel%20365%2C%20then%20there%20will%20be%20six%20match-modes%20in%20the%20function%20XLOOKUP%3B%3C%2FP%3E%3CP%3E(...)0%20-%20Exact%20Match%3C%2FP%3E%3CP%3E(...)-1%20-%20Exact%20Match%20or%20next%20smaller%20item%3C%2FP%3E%3CP%3E(...)1%20-%20Exact%20Match%20or%20next%20larger%20item%26nbsp%3B%3C%2FP%3E%3CP%3E(...)2%20-%20wildcard%20character%20match%3C%2FP%3E%3CP%3E%3CSTRONG%3E(...)-3%20-%20next%20smaller%20item%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E(...)3%20-%20next%20larger%20item%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThank%20You.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2393204%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Visitor

Hello,

Currently, there are 4 match modes available in the function XLOOKUP; 0, -1, 1, 2.

I suggest two more match modes namely -3, 3.

-3 is for the next smaller item. (exact match is skipped here.)

3 is for the next larger item. (exact match is skipped here.)

Sometimes you don't need "exact match or next smaller item."

Sometimes you don't need "exact match or next larger item."

You just need the "next smaller item" or "next larger item" to search from an array.

 

One of the solutions is -

to add + 0.0001 to the lookup value for the next larger item.

to subtract - 0.0001 from the lookup value for the next smaller item.

(We have to put as many 0s after the decimal as per the data. If the data in the array has 4 or more 0s after decimal which is not likely though, we have to take care of that. In that scenario we have to add or subtract 0.00001)

 

But in order to keep the formula clean, the below thing can be done.

 

So if the suggestion is incorporated in excel 365, then there will be six match-modes in the function XLOOKUP;

(...)0 - Exact Match

(...)-1 - Exact Match or next smaller item

(...)1 - Exact Match or next larger item 

(...)2 - wildcard character match

(...)-3 - next smaller item

(...)3 - next larger item 

Thank You.

0 Replies