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.
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.I fully support you strategy of using Tables, especially for input data that is likely to grow. If the structured references get to long to use in a formula, it is possible to define a more succinct name to refer to the longer, more descriptive structured reference. For trivial calculations I might stay within the Table and allow the Table to propagate the solution through the records, e.g.
= [@Quantity] * [@[Unit Price]]As soon as the focus shifts to more serious calculation (programming), I move the calculation away from the tables, that still hold raw data, and rely instead upon dynamic arrays
= Sales[Quantity] * Sales[Unit Price] "... or even" = quantity * unitPriceYou cannot combine the two in that spilt arrays are not accepted within a Table. I hope you are at least making sense of what I have done, even if you prefer to do something other!
p.s. The 'normal' A1 direct cell referencing was only introduced because defining variables was deemed to be 'tedious'.
- Nono, I like your approach more. Google Sheets' array formulas just work differently from what MS does with Excel. Your method is just MS's way of doing what Google does, with extra steps from what I'm seeing. I'm glad you showed me now before I had everything set up and had to start from scratch again.
Only things I'll be keeping in tables are the actual references that I need. The core values I use for calculations, so to speak.
In Google Sheets, I could write the formula in the headers. If the row was 1, then it would just place the name of that column. For everything else, it would spill the calculations of whatever I needed.
With your method, how do I go about doing the same thing? I'd like to avoid someone accidentally deleting the first row with actual data entry and removing the cells with the original formulas.
I can confirm that I have Office 365 installed on this machine.
I'll go over the code again in a bit.
Thanks for sticking through this with me though!