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

[shaniwafa]

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

 

[pella88]

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

 

[DanteAmor]

How about:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >$C$4</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; ">rose</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td><td style="background-color:#d7e4bc; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A1</td><td >=ADDRESS(SUMPRODUCT((A3:E7="rose")*ROW(A3:A7)),SUMPRODUCT((A3:E7="rose")*COLUMN(A3:E3)))</td></tr></table></td></tr></table>