Excel comparing two lists of string to return match

Question

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!

Thank you for the reply! List A would be [HDGF PKNOX1 AGO1 ZFP64 WRNIP1 ARID4B SIN3A FEZF1 and list b would be WNT5A RGS4 TUBA1A CLDN11 HDGFRP3 MSRB3 AKT3 and if we compare them basically list a's first string "HDGF" to every string in list b one by one, list c should return "HDGF" since "HDGF" is a substring (or incomplete) match with "HDGFRP3" and the other ones should return blank since they dont match with anything, so list C would show "HDGF" in the end. Hope this helps! or anything pelase let me know — Jeff The Liu
– Jeff The Liu, Commented Nov 5, 2017 at 11:14
Please edit your original question so as to show your data in an easily readable format. This kind of additional information is not really suitable for the comment format. — Ron Rosenfeld
– Ron Rosenfeld, Commented Nov 5, 2017 at 11:26
Hi guys, thank you so much for the replies. I've done that. I hope this is clearer. Please let me know! — Jeff The Liu
– Jeff The Liu, Commented Nov 5, 2017 at 11:34
To give you a handle on this, in order to conduct a comparison one needs to know the number of characters to determine a "match" and whether they have a particular position, such as at the start. For example, if a single character can determine a match than the comparison of HDGF with HPPP would result in an H in column C. If not, there should be a minimum to qualify. If a minimum of 2 characters qualify then HDGF and PPHG or HDGF and PGFP would still be matches for HG, respectively GF, if there is no rule for the matching characters' positions. — Variatus
– Variatus, Commented Nov 5, 2017 at 11:42
I see, in this case I'd want any string from list A to be a complete string for matching compared to list B for example "EGF" to "EGFR" not "EFG" or "EGFRR", list A's string has to be a complete substring in alphabetical order but do not have to be the exact string when compared to strings in list B. I hope i make sense — Jeff The Liu
– Jeff The Liu, Commented Nov 5, 2017 at 11:48

pnuts · Accepted Answer · 2017-11-05 12:00:59Z

1

For your sample, this seems to work:

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

but your question leaves open other possibilities.

answered Nov 5, 2017 at 12:00

pnuts

59.8k11 gold badges93 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeff The Liu Over a year ago

Thank you so much! I've tried: =LOOKUP(1E+100,SEARCH(A1,B:B),B:B) and it worked too. Thank you so much for your help!

Collectives™ on Stack Overflow

Excel comparing two lists of string to return match

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related