Results 1 to 7 of 7

Thread: VLOOKUP (E2000)

  1. #1
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLOOKUP (E2000)

    Hi Stephen

    I have attached a modified version of your workbook.

    In cell B1 I have used data validation to enter the list of destinations. Within the table, the weights in column I has been updated with the formula <code>=$D$1</code> so that for every destination it contains the weight entered in cell D1. The VLOOKUP formula in cell F1 looks up the relevant value in column U.

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (E2000)

    Thanks Tony worked like a charm but I would like to understand why the VLOOKUP worked as I cannot work out what it is doing

    =VLOOKUP($B$5,$A$9:$X$165,21,FALSE)

    Obviousely the $B$5 refers to the destinations that are listed in the next reference $A$9 but what does the rest mean after the colon as I cannot see where the 165,21,False comes from.

    Thanks

    Stephen

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLOOKUP (E2000)

    Hi Stephen

    <code>=VLOOKUP($B$5,$A$9:$X$165,21,FALSE)</code>


    The parameters for the VLOOKUP are:
    1. <LI>Lookup Value: In this case cell $B$5, the name of the destination. It always looks in the first column of the table array (2nd parameter)
      <LI>Table Array: The complete table that contains the lookup value and the result, in this case it has to include the whole table, $A$9:$X$165, as the lookup value is in the first column and the result is in the last column
      <LI>Column Index Number: This designates what column to look up for the result, the table has 21 columns and the result is in the 21st column
      <LI>Range Lookup: This is either true or false. False means it needs to lookup an exact value, as in this case it is looking up the destination name and needs to match it exactly. If this value is True or is omitted then it would find the next largest value that is less than the lookup value if there is no exact match. The table would need to be sorted in ascending order of the first column if you use True but does not need to be sorted if you use False.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (E2000)

    Cheers Tony that makes a lot of sense now

    Stephen

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLOOKUP (E2000)

    <P ID="edit" class=small>(Edited by Leif on 21-Sep-04 18:18. To reduce the length of the "..............................." lines which were causing the horizontal scroll bar to come into play.)</P>I am trying to make a worksheet a bit more user friendly / interactive by having cells that my colleagues can enter information into and then another which returns a result, AndrewO kindly offered a solution in 406932
    .................................................. .................................................. .................................................. .........
    In Excel, simply having another sheet with three active cells, two for entry, and one for the answer is more than adequate unless I'm missing something

    e.g.
    Cell A1 contains "Enter first thing" and Cell B1 is ready to accept that thing
    Cell A2 contains "Enter second thing" and Cell B2 is ready to accept that second thing

    Cell A3 contains "Result" and Cell B3 contains a formula, using Lookup functions

    e.g. =VLOOKUP(B1,range_on_other_sheet,col1,0) * VLOOKUP(B2,range_on_other_sheet,col2,0)

    or something of the sort
    .................................................. .................................................. .................................................. .........
    However, no matter how I try to phrase the above I cannot get it to work, I wuold very much appreciate a little more insight so that I can get it straight

    Thanks

    Stephen

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (E2000)

    Sorry Tony

    I don't mean to try your patience but I am a little confused again, if you have a moment could you please look at the attachment and try to let me know what I have down wrong / not understand.

    I decided that I need a few more rows so that I could arrive at a more correct total and in doing so thought that I had made the right adjustments to the formula that you did for me in F6, the first part remains the same $B6$,A$10$: I have then changed what was $U$ to $Y$ as this is the total column and then adjusted the the rows to 29 and the columns to 25, hence

    =VLOOKUP($B$6,$A$10:$Y$29,25,FALSE)

    But in B6 I am getting #N/A which would indicate an inappropriate operation in the above but for the life of me I cannot see it.

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (E2000)

    Sorry for butting in!

    With the 4th argument as False or 0, it's looking for an exact match, thus finding none it returns the dreaded #N/A. If you insist on not entering the whole name, try,

    =VLOOKUP("*"&$B$6&"*",$A$10:$Y$29,25,FALSE)

Posting Permissions

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