Excel - Return value based on multiple criteria and date ranges

[Eng_Noah]

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.

[#2]

@Eng_Noah 

=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.

[#3]

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.

[#4]

@Eng_Noah 

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.

[#5 · Accepted solution]

@Eng_Noah 

= 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.

[#6]

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.

[#7]

Tried this on my tables AND my example that is just a normal range. Got #NAME? error on both.

[#8]

@Eng_Noah 

=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.

[#9]

@Eng_Noah 

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.

[#10]

@Peter Bartholomew 

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!

[#11]

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.

[#12]

@Eng_Noah 

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'.  

[#13]

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.