Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup find the last row without it in the formula?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup find the last row without it in the formula?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup find the last row without it in the formula?
=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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup find the last row without it in the formula?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP, SUM, OFFSET and IF formula all at once | Excel Discussion (Misc queries) | |||
FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES | Excel Worksheet Functions | |||
Vlookup formula Excel version 2002 | Excel Discussion (Misc queries) | |||
Vlookup formula | Excel Worksheet Functions | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |