Forum Discussion
Excel - Return value based on multiple criteria and date ranges
= MAP(entryDate, itemSearched, LAMBDA(entry,search, XLOOKUP(entry, IF(itemCode=search, effectiveDate), return,"not found",-1) ))where the defined names are based on the column headings.
=XLOOKUP(1,($A$2:$A$8=MAXIFS($A$2:$A$8,$A$2:$A$8,"<="&F2))*($B$2:$B$8=G2),$C$2:$C$8)
This formula is in cell H2 and filled down.
=MAP(F2:F5,G2:G5,LAMBDA(entrydate,itemsearched,XLOOKUP(1,(A2:A8=MAXIFS(A2:A8,A2:A8,"<="&entrydate))*(B2:B8=itemsearched),C2:C8)))
MAP and LAMBDA spills the results in my example.
- Just tried both ways in my actual tables with the necessary edits for the correct references, but neither method worked.
Second one won't display anything at all in the table where I am expecting results.
First solution you gave only returns about 4 of the results out of about 200 that should be populating. The rest give me an #N/A error.I forgot to mention that the MAP and LAMBDA function only works in Office 365 or Excel for the web.
What is the expected result if "Item searched" is "Code 2" or "Code 3" and "Entry date" is 01.02.2024 or later?
=LET(filteredeffective,FILTER($A$2:$A$8,$B$2:$B$8=G2),
filteredreturn,FILTER($C$2:$C$8,$B$2:$B$8=G2),
XLOOKUP(F2,filteredeffective,filteredreturn,,-1))
This formula returns the results shown in the screenshot however it only works in recent versions of Excel.
- The expected result is from "Return This" using the most recent effective date for each Item Code.
If an Item Code's Effective date is still,for example, the 1st of January 2024 and the year is currently 6th of July 2030, it should return whatever is set for the 1st of January 2024.
