Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel novice pulling my hair out-Vlookup doing random math & can't find external links

    I am an Excel novice and new to the Vlookup function. It seems pretty straight forward but I have been trying to figure out why it has not been working for me for 2 days. On the HVoipQuote sheet, cell C24 should return a price based on the value entered in cell A24. The price is looked up on sheet1, table array is Sheet1A2:B23. Th lookup has been returning random values that are not even in the table, seems like there is some mysterious math going on. I am hoping that there is something silly going on but would love some assistance from someone that has more experience with xls. Any help is appreciated
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    Welcome to the Lounge as a new poster!

    ..see attached file.
    I just named the lookup block as handsetBlock
    For an exact match lookup, you need to include FALSE in the formula:
    =IFERROR(VLOOKUP(A24,handsetBlock,2,FALSE),0)

    zeddy
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Sep 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    AWESOME, thank you - very much. So instead of naming the table array you can just "define name" similar to setting up a drop down list?

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Vlookup notes

    Hi Fishunt

    Welcome to the world of the balding.

    A bit of background on Vlookup()
    By design only 3 of the 4 arguments :- target, Lookup table, and column position of desired data are required.

    By design and default Vlookup() will return a match based on the value that is either equal to target OR nearest to and smaller than target.

    When you append the fourth argument and specify "False" you are instructing the function to force an exact match for the target data. .. instead of nearest to or smaller than target.

    By default and design the function expects that column 1 of the lookup table to be sorted in ascending order.
    This is easy to spot with numeric data but a tad more tricky with alphanumerics. ( "a1" is not the same as "a 1")

    Use of alphanumeric data in column 1 of the lookup table is an additional reason to specify the "false" in the 4th argument.

    The inclusion of "False" as argument 4 solved your problem as it negated the default behaviour of the vlookup().

    On a copy I would suggest observing the effects of sorting your lookup table by col 1 just for your education.

    Zeddy makes a good point by naming the block. It makes the formula far easier to read.

    Have a look at the MS support site for more information.
    Hope this helps in the future.

    G

Tags for this Thread

Posting Permissions

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