finding a cell?

[Matty5894]

Hi All,

Not sure if there is a formula to do this.

Is there a way to search a a spreadsheet and return the column number that found cell is within?

also is there a way to do the same for a row?

also to make things more difficult but is preferred the search criteria would be begin within a wild care ie"*8624523"

Hope someone can help

Cheers

[ragulduy]

Normally I guess you would use the match function for this,

e.g.
=MATCH("*8624523",1:1,0)
for column number
and
=MATCH("*8624523",A:A,0)
for row number

However, you can only use that on a single row/column.

For multiple rows columns you would move to something like:
=MAX(IF(A1:J2="8624523",COLUMN(A1:J2),0))
as an array formula (confirm with ctrl+shift+enter) but that doesn't let you use wildcards, so then you be looking at:
=MAX(IF(ISNUMBER(SEARCH("*8624523",A1:J2)),COLUMN(A1:Z2),0))
as an array formula for the column and:
=MAX(IF(ISNUMBER(SEARCH("*8624523",A1:J2)),ROW(A1:Z2),0))
for the row.

[Matty5894]

AMAZING IT WORKED!!!!

Your a life saver