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.
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.=LET(filteredeffective;FILTER($A$2:$A$8;$B$2:$B$8=G2);
filteredreturn;FILTER($C$2:$C$8;$B$2:$B$8=G2);
XVERWEIS(F2;filteredeffective;filteredreturn;"not found";-1))The formula from my last reply already returns the expected result. I've only added "not found" in case the entry date is before the earliest effective date. The formula is in cell H2 and filled down. However i prefer PeterBartholomew1 's formula because it spills the results.