Hello, hope someone can assist me.

Ive 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 hadnt 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

Hi!

You can use a dynamic range:

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

Biff

"zerOman" wrote in message
...
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

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

Cordialy JB

zerOman a écrit :

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 numberof
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

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

"JB" wrote:


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

Cordialy JB

zerOman a écrit :

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