Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLOOKUP Functions

    I have several spreadsheets with numerous VLOOKUP Functions.
    I often copy the equation with the Vlookup in it down 100 or more rows where the cells in the adjacent columns have a changing search word (in most cases its an employee's name) and I I am after bring back some info about his time and production from another workbook or WS. Everything works fine byt then on down the column, I begin to get #N/A's. I check everything & its all OK. As a last resort I increase the size of the Array_Table to stretch it 20% or more beyond the range where there are any data and PRESTO, the rest of the functions all work when I copy this adjusted equation down the rest of the column. Anybody know why this is happenong?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VLOOKUP Functions

    How are you using VLOOKUP? The usual reason I have this problem is that I forget to set the table array as a fixed reference, so as I copy the formula down the intended source table array gets "left upstairs" as the relative table array in VLOOKUP formula slides down the column.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP Functions

    John:
    I do make the same mistake on occasion, but not in the situation I am talking about. First, before we go further, I am still using XL97 SR-2(1) with 128 mg Mem and a PII machine.
    This can happen even if I have used a Named-range established using =offset($A$2,0,0,counta($B:$[img]/forums/images/smilies/cool.gif[/img],columns) technique. I fix it by actually adding extra rows to the defined area.
    Actually, I meant to offer this WORKAROUND up as a TIP to anyone else that might be having the same problem. I don't want to take more of your time with this unless you would like to pursue it, say, because of an academic interest or if you might have a better "workaround".
    Thanks

Posting Permissions

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