Formula that has a dynamic ending cell reference

[Fishboy]

Hi all,

I have run into a number of problems with my Excel 2010 spreadsheet, but they all basically result to the same thing...Ranges in formulas when new data is added.

For example I have the following formula:

=INDEX('Raw Sales Data'!$J$2:$J$5000,MATCH('Dashboard'!J9,'Raw Sales Data'!$K$2:$K$5000,0))

It basically is checking 'Raw Sales Data' sheet cells K2:K5000 (Spend Amount), finding the value that matches 'Dashboard' sheet cell J9 (Spend Amount), then returning the corresponding adjacent value from 'Raw Sales Data' sheet cells J2:J5000 (Customer)

This is all good and works fine. But...

If more rows are added to Raw Sales Data, the range expands beyond J5000. Is there a formula friendly equivalent to the VBA find last row function?

In a nutshell, if 500 more rows of data are added I don't want to have to go amend all my formulas from J5000 to J5500. I also am trying to avoid just putting an exaggerated value like J10000 as some formulas really get funny about all of the blank data between J5000 and J10000

 

[Fishboy]

Sorry to revisit this issue, especially considering that Bosco-yip's solution actually did what I wanted it to do, but I have run into some issues when trying to recreate this solution for different data within the same Excel 2010 workbook.

I have tried to create dynamic named ranges on another sheet within the workbook, on a sheet called "Raw TSM Data". When I tried creating the first range for a product name (text) in column B, I used the following adaptation:

Name: Data_Item
RefersTo: ='Raw TSM Data'!$B$2:INDEX('Raw TSM Data'!$B:$B, MATCH("zzzz", 'Raw TSM Data'!$B:$B), 1)

Now, elsewhere on the spreadsheet I have this original vlookup:

=(VLOOKUP(B3,'Raw TSM Data'!$B$2:$K$19000,2,FALSE))/10000

I had hoped to replace it with:

=(VLOOKUP(B3,Data_Item,2,FALSE))/10000

Once the above formula replaces the one with actual cell references it returns #REF! and I cant understand what is so different. A knock-on effect of this means the other named ranges I have created on "Raw TSM Data" are all returning similar results from their respective vlookups.

Is there a limitation on using named ranges in vlookups, or am I doing something wrong?

 

[Fishboy]

Nevermind, I have resolved the issue by replacing my vlookups with index:match and the problem has gone away!