0

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)
    )
);
ZygD
  • 22,092
  • 39
  • 79
  • 102
DKoontz
  • 177
  • 16
  • Is this one question (re `MATCH`) or two (the first re `MATCH` and the second re your normalizing function)? You should make clear your intended connection between the two. Re `MATCH`, something like `=LAMBDA(match_mode,MATCH("x",Sheet1!$A$1:$A$10,match_mode))` with, for example, `A1`, `A3` and `A6` containing "a", "x" and "x" respectively, would return: `MyMatch(0)=3`, `MyMatch(1)=6` and `MyMatch(-1)=#N/A`. – Jos Woolley Feb 18 '22 at 05:24
  • Hi, my question was more about the drop-down feature that some functions use like XLOOKUP and its "match mode" option . I'm not sure if there is an official name. But it looks like this is not built in yet. – DKoontz Feb 19 '22 at 00:09

1 Answers1

0

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

Charles Williams
  • 23,121
  • 5
  • 38
  • 38