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.)

 

[Special-K99]

In words what are you trying to achieve?

Supply input data and your expected results.

 

[richard1234567891011]

In words yes, I need a formula that reads an area of cells, but it is also able to read if the letter is inside a cell with other letters. The result should be F54, if in that area there is at least one of these letters.

 

[richard1234567891011]

In order to explain it better.

Formula 1. Issue: If you put Y in the cell were it is also X it will not work. So if you have X and Y in the same cell the result will be 0 and It is wrong because I want F54 as result.

Formula 2: It is not able to read the area. And I need to read also the area. But it is able to give me the right result if you put also Y in the same cell. In fact if you put also Y in the cell E54 the result will be F54.
Sorry my excel It is in italian.

 

[Special-K99]

Try

=IF(SUM(ISNUMBER(SEARCH($E$54,$G$31:$G$37))+0)<>0,$F$54,0)
Array formula, use Ctrl-Shift-Enter

 

[richard1234567891011]

It is not working. the result is 0 and it should be F54. I think that the problem in your formula is the SUM. The formula has to read letters not numbers. I don't know but the SUM seems wrong to me.

 

[Special-K99]

No, the formula HAS to have a SUM since this is an array formula. The contents of SUM return 1 or 0.

Did you enter the formula using Ctrl-Shift Enter as specified ?

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[richard1234567891011]

Yes. Before I translated it in Italian. But the result is 0

 

[Special-K99]

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.