Return a cell address

[Frank Pytel]

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

 

[Don Guillett]

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

 

[Frank Pytel]

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!D265536,0)+1,4,Import!D265536)

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

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!D265536,0)+1,COLUMN(Import!D265536),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]

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)

 

[Frank Pytel]

OOhhh, Hooo Don;

"...forest for the trees!" Don't ya know.

Brilliant. I've done this so many times. I need to stop trying to force
Excel and just let it do it's job.

Thank you, Don.

God Bless

Frank Pytel

 

[Don Guillett]

Glad to help. As many know I am a proponent of the KISS principle