[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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
0
digerato
Asked:
digerato
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Patrick MatthewsCommented:
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"

For more, please see:

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
 
barry houdiniCommented:
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
 
digeratoAuthor Commented:
It seems to work, Barry! Let me test it on a much more sophisticated spread. Stand by...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Patrick MatthewsCommented:
That's some odd behavior from MATCH :)
0
 
barry houdiniCommented:
>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
 
Patrick MatthewsCommented:
You're right, not odd at all.  I hadn't fully understood the formula before I commented :)
0
 
digeratoAuthor Commented:
Great job, Barry! Everything worked, as expected. I am accepting this as a Solution.
0
 
dlmilleCommented:
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
 
barry houdiniCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now