Can "Match Mode" functionality be created within a LAMBDA function in Excel?-CodePudding

I'm just starting to play around with LAMBDA, and was wondering if it was possible to replicate the mode selection that a few formulas have. For instance when using XLOOKUP:

"Match Mode" example within XLOOKUP

Can you create these option drop downs within LAMBDA? An IF statement could be used to point to different calculations based on a 0,-1,or 1, but a user would need to know that those specific inputs mean.

I created a lambda that normalizes a selected array by column to help with linearly rescaling data, but thought it would be a great addition to allow users to pick if the array should be normalized by column, row, or the entire array.

Here's what I have so far if anyone is interested. Would the best way be to create 3 separate formulas .col .row and .all (or something)?

NORMALIZE.COL =
LAMBDA(
    array,
LET(
    rng, array,
    col, COLUMN(rng),
    rows, ROWS(rng),
    min,SUBTOTAL(5,OFFSET(INDEX(rng,1,1),,col-MIN(col),rows)),
    max,SUBTOTAL(4,OFFSET(INDEX(rng,1,1),,col-MIN(col),rows)),
    (rng-min)/(max-min)
    )
);

CodePudding user response：

At the moment there is not a way of showing drop-down options (like your XLOOKUP example) for a LAMBDA parameter when entering the formula.

I think the best you can do is to give the parameter a meaningful name like Col_Row_Array - this will be shown at formula entry