1. ## 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. ## 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. ## 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. ## Re: VLOOKUP (E2000)

Cheers Tony that makes a lot of sense now

Stephen

5. ## 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. ## 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. ## 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
•