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.
= 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.
- Figured out what my problem was and it's a silly one. I hadn't realized that I needed to name my ranges since I didn't have to do that in Google Sheets.
However, because I am using actual tables rather than normal ranges acting as pseudo-tables, I'm having the #SPILL error.
