Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup N/A (2003)

    Loungers, I can't figure this out - I hope someone can.
    In the attached sample spreadsheet I have a vlookup formula in the Noteworthy Efforts and Action tabs. For some reason some cell pickup the required info and others return an N/A error. I've checked the formulas and can't see any problems there.

    Any thoughts

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup N/A (2003)

    You are getting hit by rounding errors. Decimal fractions do not convert exactly when they are stored in the binary floating point internal format of the computer. What gets displayed will look correct since it is rounded when it is converted back to decimal for display. However, when you use exact lookups (last parameter False), this can cause the results you are seeing. You can correct this problem by converting your Ref Nums to text which won't cause you any problems if you aren't using these values in calculations. Be aware that formatting a cell that contains a numeric value as text will not convert the value to text. You need to use a procedure like the following:

    1- If the values to be converted are in B7:B100, then put the following formula in row 7 of an empty column:

    =TEXT(B7,"0.#)

    2- Copy this formula down to row 100.

    3- Select rows 7 through 100 in the column with those formulas. Select Copy from the Edit menu.

    4- Select B7, then select Paste Special from the Edit menu.

    5- In the resulting dialog box put a tick mark next to Values in the Paste section. Click OK.

    6- Select the entire column B and format all of the cells as Text.

    7- Delete the formulas added in steps 1 & 2.

    The values should now be text values, and any values you type in the column in the future should be Text values.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup N/A (2003)

    Legare,

    Thanks for the quick response - it had be stumped.

    I changed the False to True and all works fine. I'll bear your other suggestions in mind for the future reference.

Posting Permissions

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