Lookup help with non-exact dates

Question

Hello,&nbsp;Hoping for some help on a lookup function to extract data using multiple conditions. I have a table like the one below showing item numbers with price changes in the left array (column c) at certain dates. In essence the price changes for the item after the date shown in b. In the right array I would like to extract the price based on the item number and using the posting date in column E to search column B and return the first price from column c AFTER the date in e. Any assistance would be greatly appreciated!!&nbsp;&nbsp;Thanks!!&nbsp;&nbsp;&nbsp;

hansvogelaar · Answer

BJS_2024&nbsp;
In G2:
=LET(d, MINIFS($B$2:$B$1000, $A$2:$A$1000, F2, $B$2:$B$1000, "&gt;="&amp;E2), IF(d=0, "", INDEX($C$2:$C$1000, MATCH(1, ($A$2:$A$1000=F2)*($B$2:$B$1000=d), 0))))
Fill down.

peterbartholomew1 · Answer

BJS_2024&nbsp;Single dynamic array formula, with names derived from headers using 'create from selection'= MAP(Posting_Date, Item_Number,
    LAMBDA(d, item,
      LET(
        changeDates, IF(Item_Num=item, Date),
        XLOOKUP(d, changeDates, Price, ,-1)
      )
    )
  )

Forum Discussion

Lookup help with non-exact dates

2 Replies