0

WORD PROBLEM: I have a list of all biological processes involved in a disease (PROCESS TITLES) (Column A). Column C shows the genes that are commonly expressed in that disease and are a part of that biological process (GENE LIST, multiple genes per line). I want to identify the processes that are effected by a different list of genes which are altered by medications that treat the disease, to find the processes targeted by the therapies (GENE LIST, one gene per line) (Column E).

An example of a value in column A is: cell proliferation

An example of a value in column C is: BMP4|FPGS|IL18|MIF|NCF1|STAT4|VEGFA

An example of a value in column E is: ABCC1

TECHNICAL PROBLEM: I would like to know what values in column C (C2:C5379) are partial matches of any value in column E (E2:E261). I would like the column C match and the corresponding column A value to be extracted to another column, for example column G and H. Is this possible?

Example data:

Example data

Example match:

Example match

Example output:

Example output

Thanks!

2
  • If I have understood correclty, with the example you have given, there will be no match as ABCC1 is not found on its equivalent in C. Can you post sample images of data and expected results. Aug 29, 2020 at 11:42
  • That is correct, as there are 5378 processes represented in Column A/C not every one will contain a match for a value in column E. Aug 29, 2020 at 11:57

1 Answer 1

1

You can try these formulae. Assuming the search value is in cell F2, in G2, enter:

=IF(F2<>"",XLOOKUP("*" & F2 & "*",$C$2:$C$6,$A$2:$A$6,"",2),"")

In H2, enter:

=IF(G2<>"",XLOOKUP(G2,A2:A6,C2:C6),"")

enter image description here

enter image description here

If your version of Excel does not have the XLOOKUP function, try in G2:

=INDEX($A$2:$A$6,MATCH("*" & F2 & "*",$C$2:$C$6,0))

And in H2:

=INDEX($C$2:$C$6,MATCH(G3,$A$2:$A$6,0))

The results will be like in the image below (click to enlarge it). As it is a partial search, if you are looking for TNF, then TNFSF14 might be shown.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .