Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Location
    Central Victoria, Victoria, Australia
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am having trouble with vlookups that refer to a particular name range in the attached spreadsheet. Whenever I use a vlookup that refers to the table metal_costs I get a strange result - for some reason it does not work although the formula is correct. I have tried retyping the table from scratch, naming and then creating another vlookup but still get the same wrong answers.

    Does anyone have any suggestions? The cells with the wrong formulas are highlighted in light purple with white text.

    Just for some background, the data in the tables was imported from Access into the Part Details worksheet and then copied and pasted into the Metal Costs worksheet.

    Thanks.
    Attached Files Attached Files

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

    By default, it performs an approximate lookup; this only works correctly if the lookup table has been sorted ascending on the first column.

    If you add a fourth argument FALSE, it will perform an exact lookup; the lookup table doesn't need to be sorted for this.

    You need an exact lookup, so you must add the fourth argument to your VLOOKUPs. For example, in cell C13:

    =VLOOKUP($B$13,Metal_Costs,2,FALSE)

    and similar for the others. See How to Use VLOOKUP or HLOOKUP to find an exact match.

  3. #3
    New Lounger
    Join Date
    Jan 2008
    Location
    Central Victoria, Victoria, Australia
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    THANK YOU!!!!!! You have no idea the grief this had caused me.

Posting Permissions

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