Results 1 to 3 of 3

Thread: Cell address locating by text or value in a cell with address formula in a multiple columns data set

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Cell address locating by text or value in a cell with address formula in a multiple columns data set

    cell address of a particular text or value, which is in 2nd or 3rd column, (or in any column)


    A B C D E
    1
    2 Rose
    3 5132
    4


    If I wish to know the cell address for "Rose" or "5132" with address formula with address and index formula. Would appriciate if there is any other easy approch to solve this.
    Thanks in advance


    Shani

  2. #2
    Board Regular
    Join Date
    Aug 2013
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell address locating by text or value in a cell with address formula in a multiple columns data set

    Hi shaniwafa,

    Can you please give further information? In which way would you be searching for "Rose"? Will this be input in userform, in another cell or? In addition, can you please write where you want to see the result?

    Without further information, I can provide you with following formula:
    =ADDRESS(MATCH("Rose",B:B,0),2)

    This formula will search for value "Rose" in column B and show the address of the value found.

    Br
    pella88

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,187
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Cell address locating by text or value in a cell with address formula in a multiple columns data set

    How about:

     ABCDE
    1$C$4    
    2     
    3     
    4  rose  
    5     
    6     
    7     

    CellFormula
    A1=ADDRESS(SUMPRODUCT((A3:E7="rose")*ROW(A3:A7)),SUMPRODUCT((A3:E7="rose")*COLUMN(A3:E3)))
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •