IF, VLOOKUP, LEFT? & ?? please help!

[LTUser54]

I have a list of pension plan names, 477 rows long in column B, and need to match it to a list of names 224 rows long in column E, but there's a problem with my formula. Here's what I used:

=IF(VLOOKUP(B4,$E$4:$E$224,1,FALSE)=0,"","Not just a TIAA a/c")

The names don't exactly match for one thing, and I may need a totally different formula because of this. For instance, one column might have: Smith, John and the other will have: Smith, John C or: Smith, John C.

If they have the exact same name in both columns, I get "Not just a TIAA a/c", but what I really need is "Only a TIAA account" displayed in a cell if they ONLY have a name in column B

I'm stumped on this. I've tried a bunch of formulas and just can't get the result I need. Please help.

[Dav]

Strings can be truncated to search, but the danger then is getting false positives eg Smith, John Matching with Smith, David. to perform the match, you will have to give us ideas at what point to match, or if there are characters tha can be removed eg . perhaps. Then we can write a formual, is the only issue full stops

regards

Dav

[LTUser54]

Dav, thanks for your comment, I appreciate your help. The list is short enough so I can eyeball it for false positives. There are only about a dozen last names that are the same.

What I really need is for the "only a TIAA account" message to show in the cell if there is only one name in the B column vs no match in column E. Can you suggest a formula that gets this done? Is it a variation on VLOOKUP and LEFT?

Do I need to move my columns so the long one (B) is to the right of the short one (E) or do something else to make this work? Please let me know. Thanks.

[Dav]

Firstly you do not need to move the columns

Perhaps something like

=IF(SUMPRODUCT((LEFT($E$4:$E$244,10)=LEFT(B4,10))*1)=0,"Only TIAA",IF(SUMPRODUCT((LEFT($E$4:$E$244,10)=LEFT(B4,10))*1)=1,"both","more than one match criteria has failed"))

Regards

Dav

[LTUser54]

Dav, I humbly bow to your exalted expertise! Thank you so much for your help - this works perfectly. I owe you a pint (at least).... Bless you!

Mark
Boston, MA

[Dav]

Thanks for the feedback

Cheers

Dav