Oct 14 2021 09:46 AM
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
Oct 14 2021 12:57 PM
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.
Oct 14 2021 01:13 PM
Oct 14 2021 01:17 PM
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))
Oct 14 2021 01:33 PM
Oct 14 2021 01:42 PM
Apparently there is no cell with the exact value Total Down. Perhaps the cell contains extra text, even if it is only a space?
Oct 14 2021 01:55 PM
Oct 14 2021 02:00 PM
SolutionThe 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: #"
Oct 14 2021 02:15 PM
Oct 14 2021 02:25 PM
Oct 14 2021 02:27 PM
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.
Oct 14 2021 02:30 PM
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.
Oct 15 2021 07:21 AM
Oct 15 2021 07:32 AM
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")
Oct 14 2021 02:00 PM
SolutionThe 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: #"