Can vlookup find the last row without it in the formula?

[Guest]

Hello, hope someone can assist me.

I’ve taken over a task that uses the following formula in an Excel
spreadsheet:
=VLOOKUP(B5-10001,$B$2:$B$2064,1,TRUE)
The formula compares the value in an adjacent cell to all other values in
cells in the same column. The values in the cells are >=10000 and in no
specific order.

Originally the spreadsheet probably had 2064 rows and although the number of
records (rows) will always increase, my predecessors never updated the bottom
row (2064) in the formula. I noticed this error as I hadn’t used vlookup
much and was looking at the logic.

I can change the row number each time I update the spreadsheet or I guess
that I could have a used value like 10000 in cells below the last record to
say 3,000 and have 3,000 in the formula.

Is there a better way… Perhaps a function that can be used instead of the
bottom row number that recognises when it has reached the bottom row?

Thanks

 

[Biff]

Hi!

You can use a dynamic range:

http://contextures.com/xlNames01.html#Dynamic

Biff

 

[JB]

=VLOOKUP(B5-10001,Offset($B$2,,,CountA(B:B)-1,1),1,TRUE)

Cordialy JB

zerOman a écrit :

 

[Guest]

Thanks to both of you. The link to the web site will also be useful.
I can't see any buttons to rate?