Need to compare the string of two columns

Need to compare the string of two column and need the result as true/False.


A1 - suski,white A2 - Ward,William A3 - Wilson, Tony A4 - Denise ,Mary A5 - Jouswa, Stephen

B1 - Jouswa, Stephen B2 - Wilson,Tony B3 - Denise ,Mary B4 - Briggs, Matt B5 - Suski,White

Take the String in A1 and need to compare with all the string in the B column, if it present in B column .Then print True on C1.
LVL 11
Venugopal NAsked:
Who is Participating?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:

In column C, starting in C2, assuming row 1 has labels:




copy down

cheers, teylyn
Jignesh TharConnect With a Mentor Senior ManagerCommented:
Put below in C1 and copy it down
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
jigneshthar, Vlookup will work, too, but will be slower than Match(). Countif() is probably the fastest function for this. Does not make much of a difference in small files, but for large datasets it might pay off to use a fast function.
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Jignesh TharSenior ManagerCommented:
teylyn - I wan't aware about this. Do you know why that is? Doesn't vlookup end as soon it finds first exact match which is similar to Match loop?

Countif should be longest as it has to loop through entire column B to get the result.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Vlookup is slower because after finding the item it then needs to locate the column to return. This is another operation that cost a few nanoseconds. Match simply returns the row index.

Countif works in a completely different way and is super-fast.
Jignesh TharSenior ManagerCommented:
teylyn - Very interesting!! Can you point me to some resources where it comparaes various functions from performance point of view?

If Vlookup is to return same column (B:B), it doesnt necessarily have to do another lookup - assuming it is smart enough. :-)
Venugopal NAuthor Commented:
Thanks for the reply.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@Venurajav, sorry for the highjack. Maybe you get something out of it, too.

Some light reading. If you are interested in fast Excel, then read anything by Charles Williams.

Re Countif:
" If you cannot use two cells, use COUNTIF. It is generally faster than an exact match lookup:" source: (by Charles Williams)

And a discussion among Excel MVPs about what is faster: Vlookup or Index/Match (mind you, it turns out that Vlookup is faster, but they compare it with the Index/Match COMBO, where Match is nested in an Index function, and not with a simple Match on its own.

Also, there's code to time things, so if you want to give this a go ...

cheers, teylyn
Jignesh TharSenior ManagerCommented:
@Venurajav, sorry about barrage of posts for simple solution!! :-) Am still learning and I see there is long way to go...

@teylyn, thanks for all the resources. Will certainly going to digest so that I can write performant formula / code. Thanks again!! :-)
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.

All Courses

From novice to tech pro — start learning today.