Remember Me?

#1
January 6th 05, 08:43 PM
 wandering Posts: n/a
Association between words and numbers

I want to associate typed words and monetary values so that I don't have to
continually retype the price. Is there a table that I can set up that a
template or document draws from?? An example would be that everytime I type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for further
computation.

#2
January 6th 05, 09:03 PM
 Dave R. Posts: n/a

Yes- you could create a list (table) in some other sheet, then use a formula
like

=INDEX(range containing prices, MATCH(range that contains the WORDS,the cell
where you typed in PSL ...,0))

"wandering" wrote in message
...
I want to associate typed words and monetary values so that I don't have

to
continually retype the price. Is there a table that I can set up that a
template or document draws from?? An example would be that everytime I

type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for further
computation.

#3
January 6th 05, 09:04 PM
 Ken Wright Posts: n/a

Take a look at VLOOKUP. You create a table with all the matching values and
then use the VLOOKUP formula where you want the prices to appear.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"wandering" wrote in message
...
I want to associate typed words and monetary values so that I don't have

to
continually retype the price. Is there a table that I can set up that a
template or document draws from?? An example would be that everytime I

type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for further
computation.

#4
January 6th 05, 09:49 PM
 Abi Posts: n/a

and get #VALUE!

What am I missing?

"Ken Wright" wrote:

Take a look at VLOOKUP. You create a table with all the matching values and
then use the VLOOKUP formula where you want the prices to appear.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"wandering" wrote in message
...
I want to associate typed words and monetary values so that I don't have

to
continually retype the price. Is there a table that I can set up that a
template or document draws from?? An example would be that everytime I

type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for further
computation.

#5
January 6th 05, 09:58 PM
 Dave R. Posts: n/a

No one can see what you're missing.. you might try posting the formula which
returns that message.

"Abi" wrote in message
...

suggestions
and get #VALUE!

What am I missing?

"Ken Wright" wrote:

Take a look at VLOOKUP. You create a table with all the matching values

and
then use the VLOOKUP formula where you want the prices to appear.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------

--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------

--

"wandering" wrote in message
...
I want to associate typed words and monetary values so that I don't

have
to
continually retype the price. Is there a table that I can set up that

a
template or document draws from?? An example would be that everytime I

type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for

further
computation.

#6
January 6th 05, 10:15 PM
 Gord Dibben Posts: n/a

And combine the VLOOKUP with Data Validation drop-down list for easy entry of
the associated words so's you don't have to type PSL 3.5 x 9.5

See Debra Dalgelish's site for more on DV.

http://www.contextures.on.ca/xlDataVal01.html

Gord Dibben Excel MVP

On Thu, 6 Jan 2005 21:04:06 -0000, "Ken Wright"
wrote:

Take a look at VLOOKUP. You create a table with all the matching values and
then use the VLOOKUP formula where you want the prices to appear.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"wandering" wrote in message
...
I want to associate typed words and monetary values so that I don't have

to
continually retype the price. Is there a table that I can set up that a
template or document draws from?? An example would be that everytime I

type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for further
computation.

#7
January 7th 05, 10:39 PM
 Gord Dibben Posts: n/a

Abi

Post a sample(not a file) of your data layout and the VLOOKUP formula used.

Gord Dibben Excel MVP

On Thu, 6 Jan 2005 13:49:03 -0800, Abi wrote:

and get #VALUE!

What am I missing?

"Ken Wright" wrote:

Take a look at VLOOKUP. You create a table with all the matching values and
then use the VLOOKUP formula where you want the prices to appear.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"wandering" wrote in message
...
I want to associate typed words and monetary values so that I don't have

to
continually retype the price. Is there a table that I can set up that a
template or document draws from?? An example would be that everytime I

type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for further
computation.

#8
January 7th 05, 11:01 PM
 Wonderer Posts: n/a

I think the Lookup functions are the right direction. Could take me a bit to
understand by working with it. I appreciate the quick response on my question
by everyone. Cheers, Wonderer

"Ken Wright" wrote:

Take a look at VLOOKUP. You create a table with all the matching values and
then use the VLOOKUP formula where you want the prices to appear.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"wandering" wrote in message
...
I want to associate typed words and monetary values so that I don't have

to
continually retype the price. Is there a table that I can set up that a
template or document draws from?? An example would be that everytime I

type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for further
computation.

#9
January 7th 05, 11:21 PM
 Ken Wright Posts: n/a

It's easy to get lost in a formula if you don't really understand what the
formula is actually
doing:-

The VLOOKUP function essentially takes a value that you specify, whether it
be a hardcoded
number/letter/text or a value within a cell reference, and then goes and
looks it up in a table.
It will look for that value in the leftmost column of the table, and either
find it or the closest
match, and will then return the corresponding value on the same row, in
whatever column of that
table that you tell it to:-

Example - With the following table

D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40

and with your value that you are looking up in say cell A1 (and let's assume
it is 25 for
example).

You can put a formula in pretty much any other cell, that says, take the
value in cell A1 (25), go
and look for it in the lefthand column of the table (D15), and then when
you have found it (or
the lowest closest number to it), go to the second column (or whichever one
you specify if there
are more than 2) and give me the number that it is on the same row in that
column.

So, with the formula being =VLOOKUP(A1,D1:E5,2) it will first take the
value in A1 which is 25,
then go look for it in the leftmost column (D) of your specified table
(D1:E5), and it will try to
find that number. Now it isn't there, so what it will do is look for the
next lower closest
number, which in this case will be 20, and the 2 in the formula says to go
and get the value in
the 2nd column (E) in your table, that is on the same row as the 20. That
value in this case is
0.3

If you put the value 30 or 31 or 32 etc into A1 now, you will see the result
of the formula
change, because now it will either find those numbers or the lower closest
number (and in each
case there it is 30), and will subsequently return 0.35 as the corresponding
value.

Just to show you how the 2 really works in that formula, if you added one
more column to your
table so that it looked like this:-

D E F
1 1 0.20 0.15
2 10 0.25 0.25
3 20 0.30 0.35
4 30 0.35 0.45
5 40 0.40 0.55

and you actually wanted the value from Col F, then you would simpl;y change
the 2 in the formula
to a 3 to signify the third column, eg:-

=VLOOKUP(A1,D1:E5,3)

With the examples already given, 25 in A1 would return 0.35, and 30/31/32
would return 0.45

The one caveat to all of this (When getting the nearest number is OK) is
that the data in your
leftmost column must be sorted in ascending order.

There are times when you would only want it to give you a value if you had
an exact match on the
number, and in this instance you would simply add a 4th argument of 0 or
FALSE to the formula,
eg:-

=VLOOKUP(A1,D1:E5,3,0)

or

=VLOOKUP(A1,D1:E5,3,FALSE)

In these cases you do not need to have the data in your leftmost column
sorted.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Wonderer" wrote in message
news
I think the Lookup functions are the right direction. Could take me a bit

to
understand by working with it. I appreciate the quick response on my

question
by everyone. Cheers, Wonderer

"Ken Wright" wrote:

Take a look at VLOOKUP. You create a table with all the matching values

and
then use the VLOOKUP formula where you want the prices to appear.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------

--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------

--

"wandering" wrote in message
...
I want to associate typed words and monetary values so that I don't

have
to
continually retype the price. Is there a table that I can set up that

a
template or document draws from?? An example would be that everytime I

type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for

further
computation.

#10
January 13th 05, 05:03 PM
 Wonderer Posts: n/a

Turns out that for my requirements your suggestion of the Index / Match works
much better than the LookUp functions. Thanks very much!!

"Dave R." wrote:

Yes- you could create a list (table) in some other sheet, then use a formula
like

=INDEX(range containing prices, MATCH(range that contains the WORDS,the cell
where you typed in PSL ...,0))

"wandering" wrote in message
...
I want to associate typed words and monetary values so that I don't have

to
continually retype the price. Is there a table that I can set up that a
template or document draws from?? An example would be that everytime I

type:
PSL 3.5 x 9.5 it would bring up \$9.25 into a specific field for further
computation.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Surendra Excel Worksheet Functions 1 December 31st 04 12:17 PM Don Excel Worksheet Functions 1 December 29th 04 06:10 PM ron New Users to Excel 6 December 24th 04 07:32 PM Spoony_13 Excel Discussion (Misc queries) 7 December 17th 04 03:29 AM MazenBy Excel Worksheet Functions 2 November 16th 04 05:28 PM

All times are GMT +1. The time now is 09:24 PM.