Returning Cell Locaton From A Text Search

[Jim Mac Millan]

Hi,

I have been experimenting with the different functions. Vlookup, Search,
Find etc. etc. I am not understanding them or I am using them incorrectly.

I have a range A1:A30. In that range I want to find out what cell the
verbiage "SIC Code" is in. The only thing that seems to come close to what I
want to use the is the MATCH function but even that only gives me the row
#.

I actually want to find the verbiage in the cell below "SIC Code". Since
"SIC Code is a moving target I thought that to be the best reference. There
are other references I will be tracking as well but if figure this out
that'll be half the battle.

Thanks For The Help
Jim Mac Millan

 

[Dave Peterson]

One way:
=INDEX(A1:A30,MATCH("sic code",A1:A30,0)+1)

or if "sic code" is in the cell with other stuff:
=INDEX(A1:A30,MATCH("*sic code*",A1:A30,0)+1)

 

[Peo Sjoblom]

=CELL("address",INDEX(A1:A30,MATCH( "SIC Code",A1:A30,0)))

you can also get it using the address function and match

however if you always want the cell value below the match you can use

=INDEX(A1:A30,MATCH( "SIC Code",A1:A30,0)+1)

note that if "SIC Code" is in the last row and the next is empty you will
get an error
with the latter formula

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Jim Mac Millan]

Thanks Dave and Peo,

This is just what I was looking for " =INDEX(A1:A30,MATCH("sic
code",A1:A30,0)+1)"

Jim Mac Millan