Feb 12 2024 09:07 AM
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.
Feb 12 2024 09:39 AM
=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.
Feb 12 2024 11:36 AM
Feb 12 2024 01:16 PM
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.
Feb 12 2024 03:37 PM
Solution= 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.
Feb 13 2024 05:35 AM
Feb 13 2024 06:07 AM
Feb 13 2024 09:07 AM
=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 @Peter Bartholomew 's formula because it spills the results.
Feb 13 2024 09:54 AM
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.
Feb 13 2024 11:00 AM
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!
Feb 13 2024 11:21 AM
Feb 13 2024 01:29 PM
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 * unitPrice
You 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'.
Feb 14 2024 06:27 AM
Feb 12 2024 03:37 PM
Solution= 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.