What function do I use to return a certain value for specific text
 

e.g. if 5-2100 is entered into A1 then A2 should automatically = $31.00, if
5-6100 is entered into A1 then A2 should automatically = $36.00.

What function do I use to return a certain value for specific text
 

=(A1="5-2000")*31 + (A1="5-6100")*36
format as currency
--
Gary's Student


"marjoryann" wrote:

e.g. if 5-2100 is entered into A1 then A2 should automatically = $31.00, if
5-6100 is entered into A1 then A2 should automatically = $36.00.

What function do I use to return a certain value for specific text
 

marjoryann wrote:
e.g. if 5-2100 is entered into A1 then A2 should automatically = $31.00, if
5-6100 is entered into A1 then A2 should automatically = $36.00.

Hi,

Is there any connection between text and amount?

Regards,
Bondi

What function do I use to return a certain value for specific text
 

"marjoryann" wrote:
e.g. if 5-2100 is entered into A1 then A2 should automatically = $31.00, if
5-6100 is entered into A1 then A2 should automatically = $36.00.

(Please learn to repeat you entire query in the body of the posting, not
just the subject line. Some news readers truncate the latter.)

If you have only the two choices (which I doubt is the case), perhaps the
most KISS solution is an if() function, e.g. one of the following, depending
on how bullet-proof you want to be:

=if(A1="5-2100", 31, 36)

=if(A1="5-2100", 31, if(a1="5-6100", 36, ""))

If you have many choices, one of the following might work well for you
(replace "..." with additional pairs of match strings and prices; note how
comma and semicolon are used):

=vlookup(a1, {"5-2100", 31; "5-6100", 36; ...}, 2, 0)

=vlookup(a1, m1:n100, 2, 0)

where m1:m100 is the column containing such match strings as 5-2100 and
5-6100, and n1:n100 is the adjacent column containing the corresponding
prices.

Some caveats about vlookup():

1. The match strings must be in sorted order. If you feel confident, sort
them manually. Otherwise, put the match strings into a column of cells, and
use Data Sort Ascending to put them in proper order for vlookup().

2. Unfortunately, vlookup() is not bullet-proof. If no match is found, an
error (#N/A) is returned :-(. To avoid that, you might write the vlookup()
twice, e.g:

=if(iserror(vlookup(...)), "", vlookup(...))

What function do I use to return a certain value for specific
 

5-2100, etc. are accounting codes and for each code there is a specific
dollar value. There are only 8 codes.

"Bondi" wrote:


marjoryann wrote:
e.g. if 5-2100 is entered into A1 then A2 should automatically = $31.00, if
5-6100 is entered into A1 then A2 should automatically = $36.00.

Hi,

Is there any connection between text and amount?

Regards,
Bondi