Hi... hopefully someone can help.
In the following example, I need to create a formula to identify the cell address containing the string value "X"
B C D E F X A B C D E F X A B C D E F
Assuming this was row A, the formula needs to return $A$6 (or A6).
Thanks!
Assuming your range starts in A1
in A2 enter copy and paste formula and drag it across
=IF(A1="X",CELL("address",A1),"")
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Unfortunately there is no row A. What would you expect if it were in row 13 instead?
Ben Van Johnson
Sorry you are correct, there is no row A... my bad.
So if the same array were in row 13, I need to create a formula to identify the cell address containing the string value "X"
B C D E F X A B C D E F X A B C D E F
The formula needs to return $F$13 (or F13).
Thanks!
You have to be a little bit more specific. What exactly is your range? Does it starts in A13 and ends where? Is your data located in the row or in the column? Stating that row 13? Row 13 has 16,835 cells!Originally Posted by JeffRW
This will give you a relative reference. X is in cell A1 and the array is in row 13. Enter with Ctrl + Shift + Enter
Formula:
=ADDRESS(MIN(IF(A13:S13=A1,ROW(A13:S13))),MIN(IF(A13:S13=A1,COLUMN(A13:S13))),4)
This will give an absolute reference.
Formula:=ADDRESS(MIN(IF(A13:S13=A1,ROW(A13:S13))),MIN(IF(A13:S13=A1,COLUMN(A13:S13))))
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
welcome to the forum, JeffRW. try:
=ADDRESS(ROW(A13),MATCH("X",A13:S13,0))
or:
=ADDRESS(ROW(A13),MATCH("X",A13:S13,0),4)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Enter formula in A14
=IF(A13="X",CELL("address",A13),"")
B C D E F X A B C $F$13
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks