Forum Discussion
Solved
Excel - Return value based on multiple criteria and date ranges
Good day, everyone.
I am almost finished migrating to MS Excel from Google (company decision). I have a spreadsheet that automates a great chunk of work for me based on just two data entries: Entry Date and Item Searched. These 2 inputs then verify a different table with the summarized specification data that I have for my work.
The Entry Date must match or be greater than the Effective Date, but making sure it doesn't return the value of a newer Effective Date. It then looks up the Item Searched and compares it with the Item Code to ultimately return the correct Expected Return.
I had this all setup on Google Sheets with VSTACK, LET, and other functions but I just cannot seem to make it work on Excel.
Any help would be greatly appreciated.
= 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.
= 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.
- Tried this on my tables AND my example that is just a normal range. Got #NAME? error on both.
Firstly, could you confirm you are using 365, otherwise LET and LAMBDA will produce errors. Then, have you correctly identified and named the ranges
entryDate, itemSearched, itemCode, effectiveDate, return.If you wish, you could replace the defined names by direct cell referencing. As far as I know it is only me that describes the practice of direct cell referencing as an abomination that has no place in any computational framework, others get by and are happy with it.
A key feature of the approach is the -1 parameter in the XLOOKUP that returns the value from an exact match or the next largest value. This would not have worked in the old VLOOKUP days which required the data to be sorted.
=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.
