choose+match

[pierre]

PLEASE HELP ME

i entered the following formula in cell k7 :
=CHOOSE(MATCH("X",F7:F7:F7,0),5)

which means :f there is the letter X in cell F7, then give me the answer 5.

SO NOW....WHAT SHOULD I DO IN ORDER TO HAVE THE FOLLOWING ORDER :

f there is the letter X in cell F7, then give me the result 5 , and if
there is the letter Y give me the result 6 ????

THANKS

 

[Tom Hutchins]

=IF(F7="X",5,IF(F7="Y",6,""))

or possibly

=CODE(F7)-83

You didn't say what should be returned if F7 is neither X nor Y.
Hope this helps,

Hutch

 

[pierre]

isnt it possible to combine 2 fonction of : choose and match ????




formula in cell k7 :

 

[T. Valko]

isnt it possible to combine 2 fonction of : choose and match ????

Yes, you can do that but unless you're not telling us everything a nested IF
is the best solution.

=IF(F7="X",5,IF(F7="Y",6,""))

=CHOOSE(MATCH(F7,{"X","Y"},0),5,6)

 

[Tom Hutchins]

=CHOOSE(IF(ISERROR(MATCH("X",F7,0)),0,MATCH("X",F7,0))+IF(ISERROR(MATCH("Y",F7,0)),0,MATCH("Y",F7,0)*2),5,6)

Because MATCH returns the position of the character within the text being
searched, X and Y have to be the first character in F7 for this to work. If
F7 will always have only a capital X or capital Y, you could use

=CHOOSE(--(EXACT("X",F7))+--(EXACT("Y",F7))*2,5,6)

but the other formulas I sent are much more straightforward and give the
same output.

Hutch