SOLVED

Formula Help

Iron Contributor

Can anyone assist me with a formula that returns the cell reference of a search for specific Text.  Such as:

 

Find "Apple" on the sheet and return the cell reference for which Apple is found in.

 

Thank You,

 

Carl

14 Replies

@Carl_61 

Will that word occur only once or could it occur multiple times?

If it occurs only once, enter the following formula on another sheet:

 

=ADDRESS(MAX((Sheet1!A1:ZZ100="Apple")*ROW(Sheet1!A1:A1000)),MAX((Sheet1!A1:ZZ1000="Apple")*COLUMN(Sheet1!A1:ZZ1)),,,"Sheet1")

 

Change the sheet name as needed, and adjust the range if necessary.

Thank you or your assistance. I copied, pasted and changed accordingly and my result is #Value!. I wonder why this is? I know most likely what I am looking for will reside in column "A" ($A:$A) and what I am looking for is "Total Down". I'm looking for the Cell Address where this text is found.

Carl

@Carl_61 

Try confirming the formula with Ctrl+Shift+Enter.

To look just in column A, you can use the following formula on the sheet itself:

 

="A" & MATCH("Total Down", A:A, 0))

I've tried it both ways but with the newest formula ="A" & MATCH("Total Down", A:A, 0) I end up with #N/A. Using the first =Address formula and using the CTRL+Shift+Enter I get #N/A. Using the regular Check Mark I get #Value!

@Carl_61

Apparently there is no cell with the exact value Total Down. Perhaps the cell contains extra text, even if it is only a space?

To further assist, the whole reason for this is because I am trying to grab a number contained within the Cell. A report get generated where the words "Total Down: #" occupies the same cell. # meaning the number could be any number based on the results of the fresh report run. My goal is to run a formula that finds "Total Down: #" (Remember the # will represent a number) so I can extract the number from the Text/Number cell. I have the following formula =SUBSTITUTE(A66,LEFT(A66,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A66),""))-1),"") whereby I want to change out the Hard Cell Address with this formula I am trying to get to work as this cell address will always be changing. In fact the end result is the following as there is something else being searched for as well. In both cases the Cells Referenced are pulling numbers to be added together for calculations to be performed to end up with a percentage. =TEXT((317-(SUBSTITUTE(A60,LEFT(A60,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A60),""))-1),"")+SUBSTITUTE(A66,LEFT(A66,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A66),""))-1),"")))/317,"00.00%"). In this case A60 = 8 and A66 = 2. End result = 10. Percentage produced here is 96.85. Keep in mind A60 & A66 could be any A cell. If you of a better way in obtaining the same result I'm open to it.
best response confirmed by allyreckerman (Microsoft)
Solution

@Carl_61 

The formula

 

=--MID(INDEX(A:A,MATCH("Total Down:*",A:A,0)),FIND(":",INDEX(A:A,MATCH("Total Down:*",A:A,0)))+2,100)

 

will return the number # in the cell with "Total Down: #"

I've tried changing the formula to read "Total Down: 2" but its still not resulting accordingly. Ok, I just added a space and 2 to the formula and it generated A66 as the cell. ="A"&MATCH("Total Down: 2",A:A,0). So this being the case how then do I find the cells with the info I'm looking for if the number changes??

@Carl_61 

="A"&MATCH("Total Down:*",A:A,0)

Wild Card *. Got it. So now then how do I incorporate this statement into the overall formula I sent you to end up with the percentage this formula is meant to achieve? What would be your suggestion in regards to the formula knowing I have 2 things I'm looking for whereby the numbers need to be added together and acted on to achieve the percentage?

@Carl_61 

See my reply above - I already posted a formula to extract the number from the cell with Total Down: ...

You can use a similar formula for the other number, and then divide them.

@Carl_61 

Just on the off chance but has your text column got double quotes around the text. 

"Total down"

If so you would need three sets of double quotes in the formula

= ROW(INDEX(array, MATCH("""Total down""", array, 0)))
"or more recently"
= ROW(XLOOKUP("""Total down""", array, array))

In either case, the lookup returns a cell reference for the string.  Out of curiosity, what do you propose to do with an A1 style cell address that you couldn't do with a range reference.  

Morning Hans, thank you for the help you have given me with the formula. It Works!!!

Was wondering if you could help me out with one more situation? I have sort of the same situation but in this case the number I need comes from a cell that is not attached to its TAG. This is: "Grand Total Count" is the TAG Cell A34 and the information I need is in Cell D34 which is a mixed cell. At the moment D34 has "18 Units" in it. So it looks like: "Grand Total Count" (A34) "18 Units" (D34). I need the formula to find "Grand Total Count" and then grab the number from the appropriate cell. I cannot not have a hard coded ell reference to either of these items as he cell reference will always change. Here is what I have right now: =TEXT((317-SUBSTITUTE(D34,RIGHT(D34,LEN(D34)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},D34),""))),""))/317,"00.00%"). Is there anyway you could help me either rewrite this or provide me with code to insert accordingly.

Thank you,

Carl

@Carl_61 

Use

 

=1-TRIM(LEFT(SUBSTITUTE(INDEX(D:D,MATCH("Grand Total Count",A:A,0))," ",REPT(" ",255)),255))/317

 

and format the cell with this formula as 00.00%, or if you really need a text value

 

=TEXT(1-TRIM(LEFT(SUBSTITUTE(INDEX(D:D,MATCH("Grand Total Count",A:A,0))," ",REPT(" ",255)),255))/317,"00.00")

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Carl_61 

The formula

 

=--MID(INDEX(A:A,MATCH("Total Down:*",A:A,0)),FIND(":",INDEX(A:A,MATCH("Total Down:*",A:A,0)))+2,100)

 

will return the number # in the cell with "Total Down: #"

View solution in original post