How to use IF in MS Excel for getting specific column value?

[Muhammad Toseef]

I have a diseases dataset saved in MS Excel sheet, the format of the data is given below:


I have a diseases dataset saved in MS Excel sheet, the format of the data is given below:

| Object Name     | Symptom 1       | Symptom 2       | Symptom 3         |...| Symptom N        | Disease     | 
|      ABC        |     1           |     0           |     0             |...| 1                |    XYZ      |
|      ABC        |     0           |     1           |     0             |...| 0                |    XYZ      |
|      ABC        |     1           |     0           |     1             |...| 1                |    XYZ      |
|      ABC        |     1           |     1           |     0             |...| 0                |    XYZ      |

where the first column is entering variable i.e name of some object and the leaving column i.e. last column is a disease name. The columns in between are the symptoms columns for the specific disease of that object.

Now, for each row in the sheet, I want to get all the symptoms attribute where the value is 1, i.e. this symptom is present, using Disease column as a decision variable. Logic is given below.

<code>
for each row in the sheet  </code><code>
 -->  IF (Disease = XYZ)
 --> Get all the Symptoms variables where value is 1</code>

<code>

The desired result for the first rows is as follow;

Row 1 --> XYZ (Symptom1) 
Row 2 --> XYZ (Symptom2) 
Row 3 --> XYZ (Symptom1, Symptom3) 
Row 4 --> XYZ (Symptom1, Symptom2)

How can I do this in MS Excel, using IF formula? Help in this regard will be highly appreciated.</code>

 

[Eric W]

Welcome to the forum.

If you have Excel 2016 or newer, that has the TEXTJOIN function, you could do something like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Object Name	Symptom 1	Symptom 2	Symptom 3	Symptom 4	Symptom 5	Symptom N		Disease			Disease	xyz
2	ABC	1	0	0			1		XYZ			Symptom 1, Symptom N
3	ABC	0	1	0			0		XYZ			Symptom 2
4	ABC	1	0	1			1		XYZ			Symptom 1, Symptom 3, Symptom N
5	ABC	1	1	0			0		XYZ			Symptom 1, Symptom 2
6	DEF	1	0	0			1		XXX
7	DEF	1	1	1	1	1	1		XXX
8	DEF	0	0	0	0	0	0		XXX
9	DEF	1	1	0	1	0	1		XXX
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula L2 {=IFERROR(TEXTJOIN(", ",TRUE,IF(INDEX($B$2:$G$9,SMALL(IF(($I$2:$I$9=$M$1)*(SUBTOTAL(9,OFFSET($B$2:$G$2,ROW($B$2:$B$9)-ROW($B$2),0))>0),ROW($I$2:$I$9)-ROW($I$2)+1),ROWS($M$2:$M2)),0)=1,$B$1:$G$1,"")),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the formula in L2, change the ranges to match your sheet, then confirm with Control+Shift+Enter. Then drag it down the column as far as needed. Note that I included a section to exclude items such as row 8 where there are no 1s in any of the symptom columns.


If you don't have the TEXTJOIN function, you'll need some version of VBA. I can help with that, but first let me know if the output format is what you want.