MAP + XLOOKUP below a value, if not above

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

Or maybe, I can create a column with an unique ID, then the MAP+XLOOKUP would retrieve the unique ID number instead of the cost, then in a second step, a MATCH+INDEX or the like would return cost + qty for the selected ID (2 columns array), and this would spill for all IDs. Does this make sense?

Thanks for your help.

Attachment 828109

Hi again guillaume0314!

Try this formula:

PHP Code: 

=IFERROR(INDEX((S5:S23,Q5:Q23),MAP(B5:B25,C5:C25,LAMBDA(id,qty,IF(COUNTIF(O5:O23,id),TAKE(TOCOL(XMATCH(qty,Q5:Q23/(O5:O23=id),{-1;1}),2),1),""))),,{1,2}),"ALERT") 


Blessings!

Thank you John. It works well and faster than the original formula!