VLOOKUP and LEFT to match text?

[LTUser54]

Hey, kids! I'm back, with yet another attempt at frustration usin
Excel!
Oh, boy, what fun!!!!

In case you missed the last episode, I am trying to match names in
columns of text (that don't really match except for last name). Yo
would think this is a realtively easy task, but I've been trying thi
off and on (mostly on) since 11 AM...

Here's my newest failure:

=VLOOKUP(LEFT(H4,4),LEFT(B$4:B$32,4),1,FALSE)

Result is not the first 4 characters of the last name as I hoped
instead I get a #VALUE message. Note: H as LN only, b4:b32 has a tex
formatted LN,FN Middle Initial.

Can anyone tell me what I'm doing wrong? I can't figure this out a
all.

pleease, please please help...

Mark
Boston, M

 

[Biff]

Hi!

Try this:

=IF(H4="","",INDEX(B4:B32,MATCH(LEFT(H4,4)&"*",B4:B32,0)))

Biff

 

[SteveG]

Mark,

This array formula worked for me.

=INDEX(B1:B4,MATCH(H1,LEFT(B1:B4,FIND(",",B1:B4,1)-1),0))

Commit with Ctrl - Shift - Enter simultaneously, not just enter. If
you do it right the formula will have {} around it in the formula bar.
This formula assumes that the LN,FN are separated by a comma. This
matches the entire last name rather than just the first 4 characters.



HTH

Steve

 

[mark.diaz]

Biff -

YOU ARE AN EXCEL GOD!!!

I am not worthy!!!!!!!!!!!

I added in $ to the range it it works beautifully! Well done, dude, I
humbly bow to your expertise!

Mark

 

[mark.diaz]

SteveG -

What a great array formula!

Thank you so much for posting this.

I really appreciate it! Very cool, indeed!

Mark

 

[Biff]

I am not worthy!!!!!!!!!!!

LOL

Well, that makes 2 of us!

You're welcome and thanks for the feedback.

Biff

 

[SteveG]

Mark,

You're welcome. Glad to help.

Steve