Perhaps you are over complicating. Look again in the help index for INDEX.
Returns b2222
c1
b1
b2222
xxxx


=INDEX(Sheet23!J:J,MATCH("b*",Sheet23!J:J,0)+1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
Don;

That was a pretty good start. I am still lacking in my coding though. I am
getting #REF AND #VALUE errors. This is what I have come up with.

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

=INDEX(Import!D2:E65536,MATCH("B*",Import!D2:D6553 6,0)+1,4,Import!D2:D65536)

The Match()+1 returns the cell address. Now I am trying to get the
information out of that cell. Problem is that it is returning a value from
the tab I am on. The references are B1, etc. almost always B###. I need
the
text B111, or B1 or whatever.

I even tried concatenating it into a formula

=CONCATENATE("=","Import!",INDIRECT(ADDRESS(MATCH( "B*",Import!D2:D65536,0)+1,COLUMN(Import!D2:D65536 ),4)))

I have tried it with and without the "=" first reference and tried a
couple
of Indirect() calls as well.

Any ideas?

Thanks for all your help thus far. It is very helpful.

Frank Pytel



"Don Guillett" wrote:

try
=MATCH("b*",a:a,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Frank Pytel" wrote in message
...
I am trying to return a cell address based on a portion of an
alphanumeric
string.

I have a column of values on tab A that lists values like B1, B235, C3,
BR4,
etc. I want to return the address of the first cell containing
=SEARCH("B***",A:A).

Ultimately my goal is to find the search() above in column A on tab B
and
use that as a starting point for bringing those values on tab A in to
tab
B.
From there I can do a Vlookup() to import the remainder of the columns
I
need
by creating a named range.

I know I have done this before, but I can't find the file that I
created
this function in. I am not a big fan of VB and would like to avoid
this.
Can
anyone help me? I would sincerely appreciate it.

Thank You

Frank Pytel