Help creating a column with values from row next to specific text in row

[rdog6]

Hello

I have a large dataframe and I want to create a new column with gives me the value of cell on the right side of a cell that contains a specific text.

The table below is an example of my table. I want to fill the last column (Caffeine) with the values that are written on the right side of the word "caffeine" anywhere in the row. The problem is that caffeine can be written in various places (here in columns concurrent medication 1 AND 2). I have tried it with index and math, but I cannot get it to work.



does anyone know a solution for this?

PATIENT ID​	PATIENT STATUS​	WEIGHT​	DATE OF TREATMENT​	PATIENT TREATMENT NUMBER​	RESTIMULATION​	CONCURRENT MEDICATION_1​	CONCURRENT MEDICATION_1 DOSE (MG)​	CONCURRENT MEDICATION_2​	CONCURRENT MEDICATION_2 DOSE (MG)​	Caffeine
875452​		80​	02.03.22 08:41​	33​	No​	Caffeine​	200​
875452​		80​	09.02.22 11:41​	32​	No​	Propofol​	20​	Caffeine	200

 

[jtakw]

Hi,

Here's one way:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	PATIENT ID	PATIENT STATUS	WEIGHT	DATE OF TREATMENT	PATIENT TREATMENT NUMBER	RESTIMULATION	CONCURRENT MEDICATION_1	CONCURRENT MEDICATION_1 DOSE (MG)	CONCURRENT MEDICATION_2	CONCURRENT MEDICATION_2 DOSE (MG)	Caffeine
2	875452		80	02.03.22 08:41	33	No	Caffeine	200			200
3	875452		80	09.02.22 11:41	32	No	Propofol	20	Caffeine	200	200
Sheet1054

Cell Formulas
Range	Formula
K2:K3	K2	=INDEX(A2:J2,MATCH(K$1,A2:J2,0)+1)

 

[rdog6]

Hi,

Here's one way:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	PATIENT ID	PATIENT STATUS	WEIGHT	DATE OF TREATMENT	PATIENT TREATMENT NUMBER	RESTIMULATION	CONCURRENT MEDICATION_1	CONCURRENT MEDICATION_1 DOSE (MG)	CONCURRENT MEDICATION_2	CONCURRENT MEDICATION_2 DOSE (MG)	Caffeine
2	875452		80	02.03.22 08:41	33	No	Caffeine	200			200
3	875452		80	09.02.22 11:41	32	No	Propofol	20	Caffeine	200	200
Sheet1054

Cell Formulas
Range	Formula
K2:K3	K2	=INDEX(A2:J2,MATCH(K$1,A2:J2,0)+1)

HI, thank you so much for your quick response, that worked perfekt!

 

[jtakw]

You're welcome.