Dynamic range formula which finds the last cell in a dataset as the formula range too

Hi all,

I am hoping someone can help as I think I'm close, but am now going round in circles. I have attached example spreadsheet for ease of understanding.

I have a short list of Task codes "data indicators" and an extensive cost report to which these tasks codes appear. The cost report gets larger each month. What i am trying to do is identify new Task Codes in the cost report as and when i paste this report into my master template.

Ideally i want to produce a list of "new Task Codes" when i hard code the cost report into my template.

I am trying to use a "=ISTEXT(VLOOKUP(D4:D2196,'Cost Code Setup'!$M$7:$M$206,1,FALSE))" which is returning False where a new cost code is added to the cost list. I was then going to try and convert the False into "The new Task Code" then simply use a unique formula to list these, so i never need to touch, drag down etc the formula.)

The problem i am having is the lookup range at the start of the formula D4:D2196. This will require me to change the range each month as new rows of data are added to the bottom of the report. I ned this to be dynamic so i never need to touch the formula again.

Can anyone help please?

Workbook Example.xlsx


If you could solve the False into the Task Code also, that would be delightful!


NOTE: Just to clarify, the formula i am trying to write is in Cell F8. Ideally the "TRUE" results will be blank and the "FALSE" results will equal the Task Code in Column I. I can then use unique on this to identify the "New Task Codes" which need to be added to the master list in Column A. The data set in G to J will expand / grow downwards each month

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

I am assuming based on the language used in your request that you are using MS365. Is this what you mean?

=LET(nc,IF(I8:I20000="","",ISTEXT((VLOOKUP(I8:I20000,A8:A12,1,FALSE)))),c,I8:I20000,UNIQUE(FILTER(c,nc=FALSE)))

If you are NOT using Excel 2021 or MS365, then this won't work.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.