Using index and match from another source excel file

[jjk1]

Using index and match from another source excel file. Search source list for rows that match lookup value and return rows for days in worksheet of specified month plus the last day of previous month that included in the list. This works for the specified month. I do not know how to include the last day of previous month that included in the list. Please help. Also, I have to enter the specified month as a number. I have tried to modify formula so that I can use the word for the month instead, but cannot get that to work. Please help.

=LET( data, '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:L, D3Match, FILTER(data, '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!B:B=D3), monthMatch, FILTER(D3Match, MONTH(INDEX(D3Match,0,1))=E3), yearMatch, FILTER(monthMatch, YEAR(INDEX(monthMatch,0,1))=F3), IF(ROWS(yearMatch)>0, yearMatch, "No matches found") )

This does not work when I tried to use month names.
=IFERROR( FILTER( '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:L, ('[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!B:B=D3) * (MONTH('[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:A)=MONTH(DATE(F3,MATCH(E3,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),1))) ), "No matches found" )

So 2 questions: 1. the previous month question, and 2. the month word question. I don't have the knowledge to use VBA solutions.
Thank you, Jim

 

[Peter_SSs]

Sorry, I have been out of action for a week or so.

Thank you for identifying the problem. I did not have the source file open. It works.

Good news. Thanks for the confirmation.

Regarding skipping 2 rows before populating the output. In each of the grids where I would paste the formula, the 2 rows being skipped contain have text and column headers that are necessary. You were saying they have to be empty, that's problematic.

As I said before: put the formula in the row where you actually want the results to start. You cannot have hard-coded text and formula results in the same cells.

In the cell that contains the formula I would like to have a drop-down that the default position is blank and then I can select the formula in the drop down and it would run.

As I understand your suggestion, that is not possible.