I would like to find the location of a cell that contains certain text. For instance, I would like to find cell number of the cell which contains Functionviel,
For example, a cell is: Functionviel : 1.59545e+07 EUR, whose cell number let’s say A19845. Then using that I will perform certain operations like: =TRIM(MID($A$19845, 28, 7))*POWER(10,7)
How can I find which cell includes Functionviel?
>Solution :
Using these definitions:
A4:A12 is our array to search.
B1 has the text you want to find.
This formula will search your range for the desired text and return the cell address. It’s using a wildcard search.
=CELL("address", INDEX($A$4:$A$12,MATCH("*"&$B$1&"*",$A$4:$A$12,0)))
Index + Match provides advanced lookup capabilities in Excel.