Let function used with Index.

[Geir Hogstad]

Hi,

I try to figurer out how to use the new LET function, in this example wich has the same INDEX function several times. 

Is it possible, if yes, aht is the solution? 

Example file is attached.

Best regards

- Geir

[#2 · Accepted solution]

@Geir Hogstad 

Afraid that won't work without lambdas since required array of arrays. If only @Peter Bartholomew knows some trick.

[#3]

You mean like this:
=LET(Found,INDEX(t_priser,,XMATCH(J11,t_priser[#Headers])),XLOOKUP(SMALL(Found,1),Found,Found))

[#4]

Surer did. Thank you, it worked perfect.

I was a bit to fast with "best reponse" here. Sorry.

Thank you.

Best regards
- Geir

[#5]

@Jan Karel Pieterse, Can I ask what is the point of the second XLOOKUP in that equation is for?
XLOOKUP(SMALL(Found,1),Found,Found)
Doesn't that just find the smallest value then look it up in the same array and return that same value again? I guess I'm asking why SMALL(Found,1) wouldn't work.

[#6]

Since I started. It was just to practice and learn mostly. Good observation, SMALL(found,1) will work perfect.

[#7]

@Geir Hogstad 

If the objective is to examine the use of the LET function, the following demonstrates the way in which a solution may be built using formula-local names

= LET(
  Found,XLOOKUP(@Modell,t_priser[#Headers],t_priser),
  BestPrice, SMALL(Found,1),
  Utileier, IF({1,0}, t_priser[Utlleier], Found),
  XLOOKUP(SMALL(Found,1),Found,Utileier)
  )

The additional trick is to combine the seller and their prices for the vehicle type into a table and then perform the second lookup to select the best offering.

If the entire table were required as a single spilt range, that would need a plunge into LAMBDA to perform the array of array calculations and collect the results for each.

[#8]

I honestly don't know, I simply converted the formula to leverage the LET function. I did wonder about that though.

[#9]

@Peter Bartholomew 

I meant one spill for all results is required

[#10]

@Geir Hogstad 

This is just a 'cobbled' together solution.  There are standard functions which scan a function over an array and collect the results together.  The problem is that they are only standard if you have a functional programming background and I need time to study to acquire such.  What I did was to wrap the previous formula in a lambda function so that it becomes a single function call, e.g.

= ModelBestλ(5)

= LAMBDA(k,
      LET(
        Found,XLOOKUP(INDEX(Modell, k), t_priser[#Headers],t_priser),
        BestPrice, SMALL(Found,1),
        Utileier, IF({1,0}, t_priser[Utlleier], Found),
        XLOOKUP(SMALL(Found,1),Found,Utileier))
    )

I then set up a recursion passing through the indices in decreasing order.

= ForEachλ(5,ModelBestλ)

= LAMBDA(p,fλ,
    LET(
       mb, fλ(p),
       result, IF(p>1, ForEachλ(p-1,fλ), mb),
       IF(p>1, xStackλ(result, mb), mb) )
   )

 Note, the function to calculate the best price for each model is passed to this function as a parameter.  The other thing I needed was a function to add the new 'model best' to the list of results already accumulated.  For this I wrote xStackλ

= xStackλ({1,2;3,4},{5,6})

= LAMBDA(prior,new,
     LET(
        n, ROWS(prior),
        k, SEQUENCE(n+1),
        IF( k<=n, INDEX(prior,k,{1,2}), INDEX(new,{1,2})))
   )

As I suggested at the beginning, this needs considerable tidying up if it is to respond to adding model types dynamically or to be reusable for classes of related problems.