excelexcel-formulaxlookupindex-match

Xlookup multiple match types

I have a table that has a list of items, and the date in which the item changed price, however not all items change price every day so Im trying to set a formula that would return the exact match for the item and the exact match or next smallest value of a date, i tried using Xlookup similar to this

=Xlookup(1,(A1=Y1:Y10)*(B1=Z1:Z10),X1:X10,0,-1)

Item Date Price
Apple 01/01/2025 10
Apple 01/07/2025 1000
Orange 01/07/2025 5
Banana 01/06/2025 200
Item Date Xlookup Logic
Apple 01/06/2025 200 Returns Banana from 01/06/2025 instead of exact item and immediate previous date Apple 01/01/2025

However this appears to return the next closest match for both date and item which may be an entirely different item, ideally i would expect to have a Xlookup that does an exact match on one piece and a -1 match on the other, is this possible?

I also tried with Index Xmatch, however this really wants to have a row and column and using the same method as Xlookup gets the same problem.

One possible solution I can think of is to pivot the results into a crosstab but the table can get quite large so I would prefer to avoid this.

Thank you!

Solution

  • Try FILTER() and SORT() function.

    =@CHOOSECOLS(SORT(FILTER(Y2:AA5,(Y2:Y5=A2)*(Z2:Z5<=B2)),2,-1),-1)

    enter image description here