vlook up help!

zipporapper

New Member
Joined
Feb 23, 2013
Messages
12
Hi All kind excel experts out there. Need your expertise and guidance. I have a dilemma. I am trying to do a vlookup of values of Column B in Column A and return a text that says "False" in Column C, if values in Column B is not the same in Column A and empty if is the same.

The problem is Names in column B and in Column A are not exact matches due to Middle names or spacing after the comma or at times missing middle names.

My idea is i want excel to use just the Last name in column B and ignore the first name after the comma and use that last name as lookup value to lookup in Column A. Like Doe in B1 and Doe in Column A2 would be a TRUE but return as a blank in column C1. Also if there is a mismatch or there are names missing in column A when column B is used a lookup value, Column C will return the value as False or any other text that i wish. Is there anyway i could do that?

ABC
1Lee,ViceDoe,John B=vlookup(?????
2Doe, JohnLee, Vice T
3Robertson, Sinclair DHoward, Mary J
4Howard, MaryRobertson,Sinclair
5Moses, VictorPires,RobertFALSE

<tbody>
</tbody>


Appreciate your help experts! Cheers. Thanks!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try:

Excel 2010
ABC
1Lee,ViceDoe,John B1
2Doe, JohnLee, Vice T1
3Robertson, Sinclair DHoward, Mary J1
4Howard, MaryRobertson,Sinclair1
5Moses, VictorPires,Robert0
Sheet1
Cell Formulas
RangeFormula
C1=SUMPRODUCT(--(LEFT($A$1:$A$5,LEN(LEFT(B1,FIND(",",B1)-1)))=LEFT(B1,FIND(",",B1)-1)))

Not TRUE/FALSE but the result is obvious
 
Upvote 0
yeah i figured that i can just add an if function on the formula above and i can get as the result i wish. Thanks Robert for the help. May God bless your soul. For those who are visiting and wish to have the formula and apply it in your workbook. Here's the extra formula

=IF(SUMPRODUCT(--(LEFT($A$2:$A$6,LEN(LEFT(B2,FIND(",",B2)-1)))=LEFT(B2,FIND(",",B2)-1)))=1,"","False")
 
Upvote 0
yeah i figured that i can just add an if function on the formula above and i can get as the result i wish. Thanks Robert for the help. May God bless your soul. For those who are visiting and wish to have the formula and apply it in your workbook. Here's the extra formula

=IF(SUMPRODUCT(--(LEFT($A$2:$A$6,LEN(LEFT(B2,FIND(",",B2)-1)))=LEFT(B2,FIND(",",B2)-1)))=1,"","False")

I did not want to apply the IF wrapper as it just add another function so slow the calculation, but you are right- it can be done this way.
 
Upvote 0
yeah i figured that i can just add an if function on the formula above and i can get as the result i wish. Thanks Robert for the help. May God bless your soul. For those who are visiting and wish to have the formula and apply it in your workbook. Here's the extra formula

=IF(SUMPRODUCT(--(LEFT($A$2:$A$6,LEN(LEFT(B2,FIND(",",B2)-1)))=LEFT(B2,FIND(",",B2)-1)))=1,"","False")
But can't it easily return an incorrect result?
Your formula is applied here but shouldn't cell C3 be "False" since a last name of "Roberts" does not appear in column A?

Excel Workbook
ABC
1
2Lee,ViceDoe,John B 
3Doe, JohnRoberts, Vice T
4Robertson, Sinclair DHoward, Mary J
5Howard, MaryRobertson,Sinclair
6Moses, VictorPires,RobertFalse
7
Check Names 1




Instead, I propose this simpler formula, copied down.

Excel Workbook
ABC
1
2Lee,ViceDoe,John B 
3Doe, JohnRoberts, Vice TFalse
4Robertson, Sinclair DHoward, Mary J
5Howard, MaryRobertson,Sinclair
6Moses, VictorPires,RobertFalse
7
Check Names 2
 
Upvote 0
Here's another example where your existing formula gives an incorrect result, at least as far as I understand your requirement.

Excel Workbook
ABC
1
2Lee,ViceDoe,John B 
3Doe, JohnLee, Vice T
4Robertson, Sinclair DMoses, Victor
5Robertson, MaryRobertson, Sinclair DFalse
6Moses, VictorRobertson, MaryFalse
7
Check Names 3
 
Upvote 0
Hi Peter thank you for this new information. If you look at the original table by me its Lee, Vice T. But this new information provided by you greatly helps too. Thank you peter so much. I learn so much new stuff everyday from you guys. This is a shorter formula to deal with the situation. May God Bless you with more knowledge. Can you care to explain what is the function or purpose of inserting &"*" in the criteria of the countif? Trying to figure out your formula. Thanks peter again for your help
 
Upvote 0
By the way Peter, the orginal formula was provided by Robert. Your corrections to the formula you provided has helped me on the parts that we have overlooked. Seriously Thanks man! I would obliviously just apply the previous formula and thought it is fine. Haha. But Credits to Robert to he gave a headstart to me thinking on how to solve the problem
 
Upvote 0
Peter are you able to tweak your formula. For example what if two persons are having the same last name but different first names as in column B like your example above but i changed A5 Robertson, Mary to Howard, Mary. C6 Should reflect False too. but your formula reflects True which is a blank per my request. How can we solve this problem?

Check Names 3

ABC
1
2Lee,ViceDoe,John B
3Doe, JohnLee, Vice T
4Robertson, Sinclair DMoses, Victor
5Howard, MaryRobertson, Sinclair DFalse
6Moses, VictorRobertson, MaryFalse
7

<colgroup><col style="width: 30px;"><col style="width: 148px;"><col style="width: 144px;"><col style="width: 56px;"></colgroup><tbody>
</tbody>
 
Upvote 0
Hi Peter thank you for this new information. If you look at the original table by me its Lee, Vice T.
Yes, I was aware of that but I deliberately changed the last name to show you what happened with the existing formula if one last name (Roberts) was contained within another last name (Robertson).


Can you care to explain what is the function or purpose of inserting &"*" in the criteria of the countif? Trying to figure out your formula.
You said you were only interested in last names. the asterisk is a wildcard that allows us to ignore any first name(s). For a simple example
A1: Bob
A2: Bobby

The formula =COUNTIF(A1:A2,"Bob") would return 1
However the formula =COUNTIF(A1:A2,"Bob*") would return 2 because both cells begin with "Bob"



Peter are you able to tweak your formula. For example what if two persons are having the same last name but different first names as in column B like your example above but i changed A5 Robertson, Mary to Howard, Mary. C6 Should reflect False too. but your formula reflects True which is a blank per my request. How can we solve this problem?
This is a much more difficult problem, particularly since you mentioned that middle names can be there or not there and that spaces may or may not occur after the comma.

So it isn't a "tweak" but a return to SUMPRODUCT as far as I can see.

Try this. Note that I have altered the data a little once again.
I suspect this will still not be foolproof as it seems your data is inconsistent.

Excel Workbook
ABC
2Lee,VincentLee,VinceFalse
3Doe, JohnRoberts, Vice TFalse
4Robertson, Sinclair DMoses, Victor
5Howard, MaryRobertson,Sinclair
6Moses, VictorRobertson, MaryFalse
Check Names 4
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top