Results 1 to 6 of 6
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLOOKUP confusion (Excel 2000)

    Hi all,
    I need some help using VLOOKUP on the attached workbook sample. I want the yellow column to look for the number in column J, compare it to column D, and return the %inc value associated with it.

    I've read the help but it's still not clear to me. I'm a VBA-jock who is really needs to get better acquainted with the formulas <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

    Thnx, Deb
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP confusion (Excel 2000)

    Deb,

    The VLOOKUP function takes the value in the first argument (J6) and looks for the equivalent value in the left most column of the second agrument ($D$6:$G$18). If successful, the function will return the value from the column indicated by the third argument (2).

    =+VLOOKUP(J6,$D$6:$G$18,2)

    IF the optional 4th agrument is either True or omitted, as is the case with your LOOKUP, the first column in the array MUST be in ascending order. A #N/A will be returned if the value in the first arg is less than the lowest value in the lookup table.

    If the optional 4th arg is FALSE then the first column of the lookup table doesn't need to be in any order, BUT if an exact match is not found you will get #N/A.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP confusion (Excel 2000)

    Great! Thanks for the work. In the mean time, I had tried Match() and Index() and got the lookup working but I'll go back to VLOOKUP now I understand it.

    I really need to go thru J.Walkenbach's Excel Formula book so I too can be a formula-jock (for me, doing VBA is much easier than figuring out which formula to use especially when they're super nested). <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Thnx, Deb <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP confusion (Excel 2000)

    John is the KING.

    Chip Pearson also has a nice web site for things Excel.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP confusion (Excel 2000)

    A word of warning regarding vlookup. I read somewhere (Chip Pearson perhaps) that this a volatile function (or at least it was in versions prior to XP - since then I'm not sure) and that it will be recalculated every time you make a change anywhere in the workbook. This means that if you get carried away and have lots of these looking over large tables, you get lots of time to make coffee etc., If you are looking in the same range and only varying the number of columns offset you are better off using MATCH for the first lookup and then use INDEX based on the MATCH value.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP confusion (Excel 2000)

    Very true, I have also had VLOOKUP cause XL2000 to crash on saving a sheet with 23000 lines. When using VLOOKUP on a big sheet either replace the VLOOKUPs by values only, or turn off automatic calculation.

Posting Permissions

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