if statement

B

bn%$#

here is the function I'm trying to create - 1 of 6 different values can show
up in a cell. I want to be able to have the function result be: if value
number one is 111 then make the result value 1, if value number two is 222
the result value is 2, if value number three is 333 then the vaule is 3, etc.
There can be 6 different numbers in the target evaluation cell and I want to
compare it 6 different ways and if it matches the correct value compared then
the result should be a specific number. All I've been able to figure out so
far is =if(cell#,111,1). Excel looks at it as a true false statement, but I
can't get it to string together more than the one comparison.
 
R

RagDyeR

Do either of these work for you?

=MATCH(A1,{111,222,333,444,555,666},0)

=RIGHT(A1)

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


here is the function I'm trying to create - 1 of 6 different values can show
up in a cell. I want to be able to have the function result be: if value
number one is 111 then make the result value 1, if value number two is 222
the result value is 2, if value number three is 333 then the vaule is 3,
etc.
There can be 6 different numbers in the target evaluation cell and I want
to
compare it 6 different ways and if it matches the correct value compared
then
the result should be a specific number. All I've been able to figure out
so
far is =if(cell#,111,1). Excel looks at it as a true false statement, but
I
can't get it to string together more than the one comparison.
 
B

bn%$#

Thanks, yes the MATCH works but, what does the 0 functionally do. I toyed
with changing that number in the string and I get the correct result when
changing the zero to any number.
 
R

RagDyeR

The zero argument tells Match() to return the first *exact* match.

Here are the options for the third argument, as quoted from XL Help:

a.. If match_type is 1, MATCH finds the largest value that is less than or
equal to lookup_value. Lookup_array must be placed in ascending order:
....-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

a.. If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

a.. If match_type is -1, MATCH finds the smallest value that is greater than
or equal to lookup_value. Lookup_array must be placed in descending order:
TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

a.. If match_type is omitted, it is assumed to be 1.
--

a.. HTH,

a.. RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================





Thanks, yes the MATCH works but, what does the 0 functionally do. I toyed
with changing that number in the string and I get the correct result when
changing the zero to any number.
 
Top