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)


Jim Mac Millan wrote:
>
> 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

[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 :-)


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43EA5793.53A99DFB@verizonXSPAM.net...
> 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)
>
>
> Jim Mac Millan wrote:
>>
>> 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

[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" <fly_boy_jim@yahoo.moc> wrote in message
news:eAWK%235OLGHA.3460@TK2MSFTNGP15.phx.gbl...
> 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
>