# Match Values and Populate the Field in Excel

I have 2 columns (see attached) - A and C, with full or partial name match.

I need to find matches and populate corresponding values in Column B from unique values in Column D - e.g. B9=klaswen.

I will take a VB Macro or any other easy-to-use Excel-embedded function.

EE-Q.xlsx
###### Who is Participating?

Commented:
A formula like this should prevent those sort of "false positives"

=INDEX(\$D\$1:\$D\$10,MATCH(A1&" *",INDEX(\$C\$1:\$C\$10&" ",0),0))

regards, barry
0

Commented:
digerato,

VLOOKUP allows a wild card match.  For example:

=VLOOKUP(A1&"*",\$C:\$D,2,FALSE)

Be careful, though, as that could create false positives.  Consider:

1) Add "Sanchez, Juanita" to C7, and "Sanchez, Juan" to C8, and corresponding entries in Col D

2) Add "Sanchez, Juan" in A18, and "Sanchez, Juanita" in A19

3) If you extend the VLOOKUP down to those last entries, in both cases you'll get the result associated with "Sanchez, Juanita"

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html

Patrick
0

Author Commented:
It seems to work, Barry! Let me test it on a much more sophisticated spread. Stand by...
0

Commented:
That's some odd behavior from MATCH :)
0

Commented:
>That's some odd behavior from MATCH

Why's that Patrick? It's fairly standard, I think, the space added to both the lookup value and the end of the lookup range avoids matching with partial words, the second INDEX is only in there to avoid CSE....

regards, barry
0

Commented:
You're right, not odd at all.  I hadn't fully understood the formula before I commented :)
0

Author Commented:
Great job, Barry! Everything worked, as expected. I am accepting this as a Solution.
0

Commented:
barry,

Very clever - adding the space, and using INDEX to force the array inside the MATCH.  Does using the INDEX inside the match, thus alleviating CSE, actually create a more efficient formula, or is processing indifferent?

Also, is there documentation that discusses relative efficiencies of all these functions/approaches?
0

Commented:
Hello Dave

I expect that the extra INDEX function makes it less efficient - personally in my own workbooks, not for distribution, I would use the basic "array formula" version without that......but I've come across many people who don't want the added "inconvenience" of array formulas....or want to make things simpler for users, so I often suggest options for "non-array" versions, as long as the overhead isn't too high.

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.