Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Dec 2007
    Location
    Perth, Western Australia
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLookup (Excel 2003)

    Learner Driver: I have an array of around 60,000 and it changes by 2000 every 3rd day
    when I add the 2000 or so to the array, the $B$2 shown in the formula below changes to $B$2002
    and I have to manually adjust the number back to $B$2
    Is there any way around that irritation ( there are 10 vlookups so I have to change 20 formula occurences)
    =IF(ISERROR(VLOOKUP(B56920,$B$2:$C$56917,2,FALSE)) ,"/",(VLOOKUP(B56920,$B$2:$C$56917,2,FALSE)))
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLookup (Excel 2003)

    $B$2 would change to $B$2002 if you insert 2000 rows at the top of the worksheet. How are you adding the data?

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup (Excel 2003)

    Hi Rodly:

    If you want to avoid changing the range reference in your VLOOKUP Function, then lookup a Named Range, rather than an Absolute Reference.

    First, select B2:C56917, then choose Insert/Name/Define and enter a name of your choice (say LookupRange - note, spaces not allowed).

    Then, amend your Function to: =IF(ISERROR(VLOOKUP(B56920,LookupRange,2,FALSE)),"/",(VLOOKUP(B56920,LookupRange,2,FALSE)))

    Inserting rows above this range will not affect the Function.

    Regards,
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VLookup (Excel 2003)

    I take it you are aware that you are going to run out of rows fairly soon?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLookup (Excel 2003)

    In about 9 days' time, I'd guess...

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VLookup (Excel 2003)

    Give or take a couple of days. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I wasn't sure if the 56917 was before or after the last insert...
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Dec 2007
    Location
    Perth, Western Australia
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup (Excel 2003)

    Thanks Gentlemen, I'm replying in the interim, as I have not tinkered with Mr. Tasol's solution as yet.
    Apologies for not broadening the mud map of the process.
    The array information, is arrived at by a divorced database report, that gives 10 columns of data that I then paste into Excel's array.
    The array is provided with a length that converts to around six months of past time results of another process.
    When the array reaches around 62,000 rows, it is then pruned backed to 56,000 ( a simple row delete) the formula
    automatically adjusts.
    I add the report rows by "insert rows", I insert the report and the number is basically random but between 500 and 4000 rows
    and then delete unused inserted rows to make the array compact. Thats when I am obliged to adjust the formula.
    I have been doing the same process for 9 years, it works fine, but I thought it time to try a query to this group.
    Thanks, Rod.

  8. #8
    Star Lounger
    Join Date
    Dec 2007
    Location
    Perth, Western Australia
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup (Excel 2003)

    G'day Tony, thanks for the reponse, as the named range is constantly in flux, does that alter your solution?
    I need Excel to VLookup in descending order, say for example it was a lookup on athletic performance
    A Bloggs had run the 100m in 11.2 seconds on (aust date ref) 11-2-2001 then again he had run the dist
    on 23-8-2001, I need excel to result the latest run (august) the range being in decending order in array, latest being on top.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLookup (Excel 2003)

    I suspect that you are inserting the new rows above the existing data, pushing the range used in the formula "down".
    Try inserting the new rows inside the existing data, then re-sort them if necessary. You shouldn't have to adjust the formulas then.

  10. #10
    Star Lounger
    Join Date
    Dec 2007
    Location
    Perth, Western Australia
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup (Excel 2003)

    Good morning Hans, I'll try that, albeit with apprehension, I have a strong resistance to using a spreadsheet
    as a database (regards to sorting), frought with dangers, or at least, that's what I believe. Rod.

  11. #11
    Star Lounger
    Join Date
    Dec 2007
    Location
    Perth, Western Australia
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup (Excel 2003)

    G'day Hans, your solution worked fine, thank you very much indeed.
    I ended up placing an extra header row at the top, which retains the $B$2 address
    I place the inserted rows beneath, so no requirement for a sort.
    I must say excel under XP is a delight, with my old processor in 98se, I used to nod off whilst Vlookup
    processed, now its over before I blink. Cheers.

  12. #12
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: VLookup (Excel 2003)

    Rod - I had the same problem that I think you are having when inserting a column using the vlookup's. I fixed the problem by using =COLUMN() above the data, then in the vlookup formula refer the the cell containing the =COLUMN formula in the col_index_num - ie instead of being 2 it could be B1.

    That way you can insert extra columns without having to reallocate the col_index_num

    I hope that might help

  13. #13
    Star Lounger
    Join Date
    Dec 2007
    Location
    Perth, Western Australia
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup (Excel 2003)

    Onya, Dean, I'll keep that in mind, should I need to insert columns.
    My problem was inserting rows, causing a random array count.
    Looks like it has been solved, I'll keep both my sheets for my next vlookup
    to make sure they both are identical. Rod.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •