Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    409
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VLOOKUP function (2002)

    I have a question on how the VLOOKUP function works (Excel XP and WinXP). I have a worksheet which includes 65+ years of daily data in the form of:

    A B
    1 Date Daily Mean Streamflow
    2 1/1/1939 29
    3 1/2/1939 22
    4 1/3/1939 18
    5 1/4/1939 15
    6 1/5/1939 13

    24054 2/5/2005 67
    24055 2/6/2005 63

    I needed to sort the data into days of the year in rows and each year in columns. I therefore set up a worksheet in the following format:


    A B C D
    1 Day Month 1939 1940
    2 1 1 29 19
    3 2 1 22 12
    4 3 1 18 8
    5 4 1 15 5
    6 5 1 13 3

    In each of the cells, columns C - BP, I used this formula to get the data:

    =VLOOKUP(DATE(C$2,$B3,$A3),Data_Range,3)

    Data_Range is defined as $A$1:$B$24055. The data are consecutive and there are no blank rows for (potentially) missing dates. The formula worked fine, unless the daily data (Data_Range) did not contain any data for the date referenced in the formula. What VLOOKUP did was to take the last valid data point and fill in all the cells until a new valid data point was encountered. For example using the above tables, if there was no data for the days 1/3/39 and 1/4/39 the completed table would look like:

    A B C
    1 Day Month 1939
    2 1 1 29
    3 2 1 22
    4 3 1 22
    5 4 1 22
    6 5 1 13

    (1) Is the way that VLOOKUP was designed to work?
    (2) Is there a way to modify the setup and or formula so that if no data exists in the data range, then a blank is inserted rather than the last valid number?

    Thanks for the help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLOOKUP function (2002)

    Use Vlookup with the last parameter as FALSE to ensure an exact match. If you put no paramater, it is assumed that you want an approximate match.
    =VLOOKUP(DATE(C$2,$B3,$A3),Data_Range,3,False)

    Ones with no matches will then yield an #n/a error.

    After you create all the vlookup formulas, copy the range, pastespecial -values to turn them all into values

    Now select the range, edit - goto - special - constants - check "errors" and all the "errors" will be selected. Hit <delete> and it will clear those cells.

    If you want to keep the formulas you could also use:
    =If(isna(VLOOKUP(DATE(C$2,$B3,$A3),Data_Range,3,Fa lse)),"",VLOOKUP(DATE(C$2,$B3,$A3),Data_Range,3,Fa lse))

    and this will have put a null string (instead of the N/A) in the cells will no data. You could change the "" to something else (eg "n.d.", or "no data", etc) if desired. A formula can not return an empty cell. Eliminating the errors after the fact (as described above) is the only way, though this way is not "live" in case you get data later.

    Steve

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    409
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP function (2002)

    THANKS!
    I was semi-aware of using false statements, but the "Goto Errors" is brand new to me. Your efforts (and quick response) are appreciated!

Posting Permissions

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