Hi SongBear

Thanks for that - it works well now - pity I cannot just type in a town etc
- would make it a bit quicker.
Probably now look at the data sheet to see if I can clean it up a bit.
Thanks again
--
Bruge


"SongBear" wrote:

Hi Bruge
For one thing, If you use the 0 at the end of your formula, the match to the
list has to be exact. This means that your spelling, spaces and such in your
lookup value in B4 have to be exactly like what is in the list to find a
match in the list. If you use a 1 at the end of the vlookup formula, it will
find a "close enough" match...which can be inaccurate much of the time, so my
advice: stick with the 0 but find a way to get an exact match. A way is
discussed below, but your list is 18,000 long and that may make it messy.
here is what I have so far...
I created a sheet to test this and filled it with random yucky. I named the
sheet Heights Data.

ColA ColB ColC ColD ColE ColF
Town name County Zip Latitude Longitude Height (above Sea Level)
houstosn haris 71000 1.2 2.1 100
spring isar 72000 2.3 3.2 200
conroe aris 73000 3.4 4.3 300
tombal buggy 74000 4.5 5.4 400


I then created a dropdown list using the Data|Validation tool as follows:

I selected my town names in column A on the 'Heights Data' worksheet and
created a range name: "Town_Names".
(how to create range names is in the help menu, if needed. Let me know if
you need help with that.)

I then went to a new sheet, selected B4 and went to Validation on the Data
menu.
In the Validation dialog box, i selected the settings tab, then "List" under
Allow:, and typed in "=Town_Names" (no quote marks) in the Source: box. Then
OK.

(Further instructions on how to do this: in the Excel help box, type:
"dropdown list"
then select the topic:
"Create a drop-down list from a range of cells"

What this gained me was a dropdown box in B4 on the calculation sheet with
names exactly as they appeared in the data table.

I then created these five formulas in the next five cells to the right:
C4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,C3,0)
D4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,D3,0)
E4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,E3,0)
F4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,F3,0)
G4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,G3,0)

Note that I used the cells above the formulas (C3 to G3) to contain the
column numbers that I wanted to retrieve. In this case I skipped the Town
Name column as it was already in B4 and placed 2 through 6 consecutively in
the cells above.
I then used the drop down list in B4 to select town names. Typical results
are as follows, compare with my original list:

B C D E F G
3 2 3 4 5 6
4 spring isar 72000 2.3 3.2 200

B C D E F G
3 2 3 4 5 6
4 conroe aris 73000 3.4 4.3 300

B C D E F G
3 2 3 4 5 6
4 houstosn haris 71000 1.2 2.1 100

If I typed in a name in B4 (before I created the dropdown list), and it was
not exact, I got #NA in the lookup formulas, just as you did, so the probable
problem was in not having exact matches in the Heights Data town name list.
In some cases there are hidden problems with imported data, such as spaces in
front of each name in the Town Names list, in which case, if you didn't
discover it, you would NEVER get a match. Frustrated forever.
Hope this helps, please let us know if it helped or if any of this needs
further clarification.
SongBear

"Bruge" wrote:

*see* I am going barmy now - the reference B4 in my formula is the type in
box on Sheet 1 and the references A2:F18000 are the fields on sheet 2, actual
formula is

VLOOKUP(B4,'Heights Data'!A2:F18000,1,0)
--
Bruge


"Bruge" wrote:

I have read with interest the content of this discussion since it seems to be
the answer to my problems.
I have a spreadsheet containing approv 18 000 records of data relating to
latitude etc for all Towns, Villages etc and wish to design a search facilty
on a separate worksheet which will allow me to type in a particular Town name
and then present that row of information on the sheet.
From that information I can then apply a special calculation based on the
information from two or three cells.
I have been trying to use VLOOKUP but keep getting #N/A despite many
attempts, trials etc.

the basic layout is :

ColA ColB ColC ColD ColE ColF
Town name County Zip Latitude Longitude Height (above Sea Level)

my formula is: VLOOKUP(B4,A2:F18000,1,0)

Can anyone offer help please?

--
Bruge


"SongBear" wrote:

maestro
What you described sounds like what I provided, i think. You would put that
formula anywhere on the spreadsheet. You would have that type in cell
anywhere, as well.
If you build the example as I presented it in a clean sheet, you will
understand what I am talking about. The fact that I used different words...OK
i just checked something, you need to us this formula if the list of text is
not sorted alphabetically.
VLOOKUP has 4 terms, the last one must be false for unsorted lists.
=VLOOKUP(B2,B4:C13,2,FALSE)
For the first term, in the example B2, put in the address of the cell where
you will be typing in the text, place the formula where you want the number
to appear.
The second term (after the first coma) is the table address.
The third term (after the second coma) is the column in the table to return
a value from.
The fourth term, is False for un-sorted lists where you need an exact match,
it will return #NA if you spell the lookup item wrong, but you can provide
dropdown list functionality to overcome that.
Lets build a more complex example.
Lets add a third column and a fourth column to the table, the third will be
a mystery for this example, it is there for a place holder and could be
anything, whatever is in it, we don't need or care...but the all important
fourth column is were we find the shipping weight.

Here is the small example table:
J K L M N
2 ITEM COST Desc. SHIIPPING
3 Free sample $- Yadda0 0.75
4 text1 $12.56 yadda1 1.22
5 text2 $18.44 yadda2 2.11
6 Green Widget $0.33 yadda3 3.21
7 Blue Widget $15.95 This... 1.23
8 NotA Widget $1.98 yadda5 4.56
9 Yellow Widget $6.01 yadda6 7.53
10 text7 $8.23 yadda7 1.59
11 text8 $4.44 yadda8 9.51
12 text9 $300.00 yadda9 3.57
13 text10 $6.75 yadda10 8.52

15 Shipping: 0.12

Note that the table is built in columns K through N and the data is in rows
3 througn 13. Yours can have more rows and even columns. Column J is used to
orient you in the example. Now lets build an order sheet.
For the example I added a 'shipping factor' in L15, it could have been
anywhere.
Back in Column B, I set up a lookup cell to get the description of any item
(I decided the description is in that second column) The lookkup formula goes
in cell C4 and it references cell B4 where you type in the item name; the
formula in C4 is:
=VLOOKUP(B4,K3:N13,3,FALSE)
Note that i referenced the third column in the list as the third term in the
formula. On the spread sheet it looks like this:

Get Item Description He
Blue Widget This is a full description of this item.


The words "This is a full description of this item." are in the third column
of the list for the Blue Widget. It is not all vislible in the list because
the column is too short, but the text is there. It becomes visible in the
lookup box.

NEXT: below the description lookkup, we build an ordersheet.

Starting in row 10 with headers, and continuing below that with price and
shipping lookups and calculations...
In operation, it will look like this:
Select Order He Cost Shipping
Yellow Widget $6.01 $0.90
NotA Widget $1.98 $0.55
Text7 $8.23 $0.19
text2 $18.44 $0.25
Green widget $0.33 $0.39
text1 $12.56 $0.15

Totals $47.55 $2.43
Order Total $49.98

I just entered the lookup function twice, once in the first order cost cell
(Next to where the Yellow Widget is being ordered) and once in the shhipping
cost box in the next cell over. I then copied down the two formulas to the
next few cells below...
I will show the cost column first. This message tool is too narrow to show
both at once.

Select Order He Cost
Yellow Widget =VLOOKUP($B11,$K$3:$N$13,2,FALSE)
NotA Widget =VLOOKUP($B12,$K$3:$N$13,2,FALSE)
Text7 =VLOOKUP($B13,$K$3:$N$13,2,FALSE)
text2 =VLOOKUP($B14,$K$3:$N$13,2,FALSE)
Green widget =VLOOKUP($B15,$K$3:$N$13,2,FALSE)
text1 =VLOOKUP($B16,$K$3:$N$13,2,FALSE)

Totals =SUM(C11:C17)
Order Total

The next column over is set up like this...
Shipping
=VLOOKUP($B11,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B12,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B13,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B14,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B15,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B16,$K$3:$N$13,4,FALSE)*$L$15

=SUM(D11:D17)
=C18+D18

If this is not the kind of thing you are describing, I would need more
explaination of what you are trying to do. It sounds like, from your
description, that you want to do what is accomplished in the 'cost' column
above. Is that not it?

NOTE: This Microsoft article may be helpful:

http://office.microsoft.com/en-us/ex...CL100570551033

I found this article personally helpful about a week or so ago:

http://office.microsoft.com/en-us/ex...CL100570551033

Also try:

http://office.microsoft.com/en-us/ex...CL100570551033

Please let us know if this helps, if not, maybe you can give us a more
detailed description of what is different about your application so that I
can see better how to help.

SongBear


"maestro" wrote:

I think I did a poor job of explaining myself. My apologies. I plan to have
a table like you described with a column of text and a column of associated
prices.
Example:

A B
blue widget $15.95

These columns will contain dozens of rows. When I type "blue widget"
somewhere else on the worksheet, I want it to bring up the associated price
and drop it into another cell.

Does this make sense?

Regardless, thank you very much for responding so quickly.

Maestro

"SongBear" wrote:

*Sigh* I meant to say that the formula in Cell C5 is: =VLOOKUP(B2,B4:C13,2)

"SongBear" wrote:

maestro
Generally speaking, yes there are a couple of ways to go.
For instance if your table is one column of text lables and one column of
values, then you would use a VLookup function.
There is a very good explaination of VLOOKUP in Excel help, along with other
table lookup functions.
Example:
the formula in cell I5 is: =VLOOKUP(B2,B4:C13,2)

B C
5 text6 753
6
7 text1 122
8 text2 211
9 text3 321
10 text4 123
11 text5 456
12 text6 753
13 text7 159
14 text8 951
15 text9 357
16 text10 852

Please let us know if this answers your question.