MAP + XLOOKUP below a value, if not above

[guillaume0314]

Dear All :

I use this function:

=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")))

from an old post at https://www.excelforum.com/excel-for...ml#post5817525

Purpose is to get 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.

It works fine, however, instead of getting only the COST (1 column: Q5:Q23), I would like to get a 2-column array with the Qty as well {Q5:Q23;O5:O23}

This would prevent me from using the formula twice, on 2 different columns, which is quite resource consuming. Instead, I could get both results with a single XLOOKUP.

Considering there would be an issue with the TOCOL, how can we achieve this? Thanks a lot.

map_below_above.jpg