software

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: www.excelforum.com/member.php...oamp;userid=33459
View this thread: www.excelforum.com/showthread...hreadid=544442Hi!

Try this:

=IF(H4=quot;quot;,quot;quot;,INDEX(B4:B32,MATCH(LEFT(H4,4)amp;quot;*quot;,B4: B32,0)))

Biff

quot;LTUser54quot; gt; wrote in
message ...
gt;
gt; Hey, kids! I'm back, with yet another attempt at frustration using
gt; Excel!
gt; Oh, boy, what fun!!!!
gt;
gt; In case you missed the last episode, I am trying to match names in 2
gt; columns of text (that don't really match except for last name). You
gt; would think this is a realtively easy task, but I've been trying this
gt; off and on (mostly on) since 11 AM...
gt;
gt; Here's my newest failu
gt;
gt; =VLOOKUP(LEFT(H4,4),LEFT(B$4:B$32,4),1,FALSE)
gt;
gt; Result is not the first 4 characters of the last name as I hoped,
gt; instead I get a #VALUE message. Note: H as LN only, b4:b32 has a text
gt; formatted LN,FN Middle Initial.
gt;
gt; Can anyone tell me what I'm doing wrong? I can't figure this out at
gt; all.
gt;
gt; pleease, please please help...
gt;
gt; Mark
gt; Boston, MA
gt;
gt;
gt; --
gt; LTUser54
gt; ------------------------------------------------------------------------
gt; LTUser54's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33459
gt; View this thread: www.excelforum.com/showthread...hreadid=544442
gt;

Mark,

This array formula worked for me.

=INDEX(B1:B4,MATCH(H1,LEFT(B1:B4,FIND(quot;,quot;,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: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=544442Biff -

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!

MarkSteveG -

What a great array formula!

Thank you so much for posting this.

I really appreciate it! Very cool, indeed!

Markgt;I am not worthy!!!!!!!!!!!

LOL

Well, that makes 2 of us!

You're welcome and thanks for the feedback.

Biff

gt; wrote in message ups.com...
gt; Biff -
gt;
gt; YOU ARE AN EXCEL GOD!!!
gt;
gt; I am not worthy!!!!!!!!!!!
gt;
gt; I added in $ to the range it it works beautifully! Well done, dude, I
gt; humbly bow to your expertise!
gt;
gt; Mark
gt;

Mark,

You're welcome. Glad to help.

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=544442