Issue with a formula

[richard1234567891011]

I have two formulas. But both of them are not working in the right way.
the first formula is:
=IF(COUNTIF(G31:G37,$E54),$F54,0) issue: It is not able to read a cells were inside are more than 1 letters. (E54=X) So the main problem that I have with this formula is that is not able to read a cell, like I have, were inside there are more letters than 1, like X, Y, Z and the result of this formula give me 0 instead of F54.
the second formula is:
=IF(ISNUMBER(FIND($E54,G31)),$F54,0) issue: It reads only a cell and not a area like the previous one. But contrary to the previous one it is able to read an X,Y,Z in the same cells and it is able to give me as result F54 if there is an X (E54=x)

My issue at the end is: I would like to have a formula that it is a mix of both. A formula able to read an area (like the first one) and able to read a letter in a cell were inside there are also other letters. (like the second one.)

 

[richard1234567891011]

Do this

1. Create a blank sheet
2. In E54 put A
3. In F54 put X
4. in G31 put 123A5
5. in G32 put BCDEF
6. in a blank cell anywhere on that sheet enter
=IF(SUM(ISNUMBER(SEARCH($E$54,$G$31:$G$37))+0)<>0,$F$54,0)
Array formula, use Ctrl-Shift-Enter

Result is X the value in F54
The formula has searched G31:G37 for the value in E54 "A" and found it in the middle of G31.

7. Now change E54 to G - result is 0 because G does not exist in G31:G37

Check the value in E54 on your main spreadsheet - ensure it is ONE character long and it does appear in G31:G37 since the above formula clearly works.

In mine is not working man.

 

[Special-K99]

You didnt use Ctrl-Shift-Enter or there would be curly brackets around the formula {}

 

[richard1234567891011]

You didnt use Ctrl-Shift-Enter or there would be curly brackets around the formula {}

Ahahah ok. Array formulas? Never used. I didn't even know what was. In any case yes!!!!!! now it is working. that formula is amazing. Thank you very much.