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

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

Here's my newest failu

=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 text
formatted LN,FN Middle Initial.

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

pleease, please please help...

Mark
Boston, MA


--
LTUser54
------------------------------------------------------------------------
LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459
View this thread: http://www.excelforum.com/showthread...hreadid=544442

Hi!

Try this:

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

Biff

"LTUser54" wrote in
message ...

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

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

Here's my newest failu

=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 text
formatted LN,FN Middle Initial.

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

pleease, please please help...

Mark
Boston, MA


--
LTUser54
------------------------------------------------------------------------
LTUser54's Profile:
http://www.excelforum.com/member.php...o&userid=33459
View this thread: http://www.excelforum.com/showthread...hreadid=544442

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=544442

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

SteveG -

What a great array formula!

Thank you so much for posting this.

I really appreciate it! Very cool, indeed!

Mark

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

LOL

Well, that makes 2 of us!

You're welcome and thanks for the feedback.

Biff

wrote in message
ups.com...
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,

You're welcome. Glad to help.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=544442