mercredi 21 octobre 2015

If a Excel cell in Column A contains a substring from a list in Column C, can that substring be returned in Column B?

So I have a list of stems in ColC, and I want to search through ColA to see:

(1) Does the cell contain one of the substrings? (2) Which substring does it contain?

I can do the first question in a variety of ways, including this: =INDEX(C2:C25,MATCH(FALSE,ISERROR(FIND(""&C2:C25&"",A2)),0))

I just can't do the second, and return the value into ColB.

For example, A2 abacavir contains 'vir' which is in the list (C25). I want B2 to say 'vir', rather than 'true' or '1'.

Also, is there any way that ColB could give multiple values if the cell in ColA contained more than one value from the list?

Thanks so much for any help you can offer :-) from a frustrated graduate student trying to speed up analysis of a 8,000-row spreadsheet!

sample of spreadsheet

Aucun commentaire:

Enregistrer un commentaire