I am fairly advanced in Excel but can't seem to find a way to locate the cell address of specific text. For instance the text "Cat" appears only once somewhere in a known range. I am looking for a formula similar to =address or =Cell("address" or perhaps =vlookup that will let me input 1) the text Cat and 2)the range to look in and then have it return the address of where it finds the text "Cat". Is this possible?
hi DMumme. say the known range where "cat" is will be column C. then simply use:
=ADDRESS(MATCH("Cat",C:C,0),3)
or
="C"&MATCH("Cat",C:C,0)
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
benishiryo
Works perfect. Many thanks!
Last edited by DMumme; 05-13-2015 at 07:14 PM.
Say the known range is A1:O23 then array-enter this formula outside that rangeArray formulas are not committed in the regular way. You commit by pressing and holding Ctrl + Shift as you hit Enter. You will know that it has been entered successfully when you can see curly braces {} around your formula in the formula bar. You do not type these in yourself. Excel does it for you.Formula:
=ADDRESS(MAX(IFERROR(SEARCH("cat",A1:O23),0)*ROW(A1:O23)),MAX(IFERROR(SEARCH("cat",A1:O23),0)*COLUMN(A1:O23)))
Last edited by FlameRetired; 05-13-2015 at 01:26 AM. Reason: formula change
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks