CODEWITHSUNDEEP
excelstringlistcomparematch
Jeff The Liu
Jeff The Liu

Reputation: 71

Excel comparing two lists of string to return match

I have looked through google and found many similar questions but none of the suggested formulae really worked for me. I am trying to do something very simple: I am basically comparing two lists (list A and list B) of strings (namely genes) and having a third list (list C) that returns matches from list A to list B. The matches do not have to be exact in fact many aren't so I would need list C to return strings that have a partial or complete match from list A to list B (ex. List A's "EGF" is a match to list B's "EGFR", and return "EGF" in list C). Please help, I tried: if(iserror(search(list A's first string, entire list b),list a's first string, "") but it does not work. Please advise and thank you!!!!!!!!!!!

Example:

List A = HDGF PKNOX1 AGO1 ZFP64 WRNIP1 ARID4B SIN3A FEZF1 SIX2

List B = WNT5A RGS4 TUBA1A CLDN11 HDGFRP3 MSRB3 AKT3 SIX2

I'd like to compare list A's "HDGF" to every string i.e. "WNT5A", "RGS4" ... etc one by one to find a match (match does not have to be exact)

So "HDGF" would match with "HGDFRP3" since it is a substring of it so List C would return the matches

List C = HDGF SIX2

Hope this helps!

Upvotes: 0

Views: 767

Answers (1)

pnuts
pnuts

Reputation: 59495

For your sample, this seems to work:

=index(B:B,match(A1&"*",B:B,0))

but your question leaves open other possibilities.

Upvotes: 1

Related Questions