Results 1 to 7 of 7
Thread: VLOOKUP (E2000)

20040920, 09:23 #1
 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.

20040921, 07:15 #2
 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

20040921, 08:05 #3
 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:
 <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.

20040921, 09:07 #4
 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

20040921, 17:18 #5
 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 21Sep04 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

20040922, 07:34 #6
 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.

20040922, 09:19 #7
 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)