BYROW retrieve value in a dynamic array

Dear friends:

I need your help. I am trying to retrieve a cost value from a dynamic array, however I am stuck with my BYROW function. Not sure what I did wrong here.

What I need, the cost which corresponds to the exact qty or the qty immediately below the requested quantity or, if it does not exist, the cost immediately above. The value would return "ALERT" il it does not exist.

Any help is much appreciated on this matter, thanks.


retrieve value by row.jpg

Hi to all!

Try this formula instead:

PHP Code: 

=MAP(A5:A25,B5:B25,LAMBDA(id,qty,IF(COUNTIF(M5:M23,id),TAKE(TOCOL(XLOOKUP(qty,O5:O23/(M5:M23=id),Q5:Q23,,{-1,1}),2),1),"ALERT"))) 


Blessings!

Thank You John it works great.

I have a question. First time I see this kind of thing within an XLOOKUP function:

XLOOKUP(qty,O5:O23/(M5:M23=id),Q5:Q23,,{-1,1})

Is it some sort of a filter? Like the filter function but called with a "/" like a shortcut?

Thank you.

Hi guillaume0314!

The "/" sign is the math division. In this case, the division is between a range and a matrix (comparison between M column and each id). When M column match with the id, the comparison gives TRUE, otherwise FALSE.
When you do a math operation with a logical value, it is converted to a equivalent value of 1 (if TRUE) or 0 (if FALSE). If TRUE, the number of column M is given, otherwise a #DIV/0! error. XLOOKUP ignores the error and
make the lookup just with the numbers that accomplish the condition.

I hope that the explanation helps you. Blessings!

Thank you for the details. I now understand the logic and I find this extremely powerful in order to filter data fast and easily prior to performing an XLOOKUP.

Thanks a ton.