I have a data table with text fields and associated values. In another part
of the spreadsheet I need for Excel to match a text entry (when I type it) to
the table and copy the associated value to another cell in the worksheet. Is
this possible?

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.
If you need more help or clarification, let us know.
SongBear

"maestro" wrote:

I have a data table with text fields and associated values. In another part
of the spreadsheet I need for Excel to match a text entry (when I type it) to
the table and copy the associated value to another cell in the worksheet. Is
this possible?

*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.
If you need more help or clarification, let us know.
SongBear

"maestro" wrote:

I have a data table with text fields and associated values. In another part
of the spreadsheet I need for Excel to match a text entry (when I type it) to
the table and copy the associated value to another cell in the worksheet. Is
this possible?

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.
If you need more help or clarification, let us know.
SongBear

"maestro" wrote:

I have a data table with text fields and associated values. In another part
of the spreadsheet I need for Excel to match a text entry (when I type it) to
the table and copy the associated value to another cell in the worksheet. Is
this possible?

Hi

Assuming the cell you enter Blue widget is D2 and you want the price
returned in E2.
In cell E2
=VLOOKUP(D2,$A$B,2,0)

If you wanted to do in on another sheet, say Sheet2, but with your
lookup table on sheet1, then
=VLOOKUP(D2,Sheet1!$A$B,2,0)

--
Regards

Roger Govier


"maestro" wrote in message
...
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.
If you need more help or clarification, let us know.
SongBear

"maestro" wrote:

I have a data table with text fields and associated values. In
another part
of the spreadsheet I need for Excel to match a text entry (when I
type it) to
the table and copy the associated value to another cell in the
worksheet. Is
this possible?

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.
If you need more help or clarification, let us know.
SongBear

"maestro" wrote:

I have a data table with text fields and associated values. In another part
of the spreadsheet I need for Excel to match a text entry (when I type it) to
the table and copy the associated value to another cell in the worksheet. Is
this possible?

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.
If you need more help or clarification, let us know.
SongBear

"maestro" wrote:

I have a data table with text fields and associated values. In another part
of the spreadsheet I need for Excel to match a text entry (when I type it) to
the table and copy the associated value to another cell in the worksheet. Is
this possible?