How to Convert a Multiple Response Survey Question to Binary Responses|General Excel Questions & Answers|Excel Forum|My Online Training Hub

In the attached file you can see that respondents were asked to identify in columns A through H, which of the 15 hospitals their family had visited in the past year. Each hospital is identified as 1 through 15. Respondents could answer this question in any order and could identify up to 8 hospitals. I want to convert these answers so that columns I through W will have a 1 or 0.

So, in row 2, the respondent indicated that they visited hospital 3. I would like to find that 3 and insert a "1" in column K for IP3 and all of the rest of the columns (I through W) for that first respondent will be replaced with zeros.

You can see in row 8, I would like the lookup function to find all 8 numbers and insert 1's in for hospitals 3, 4, 6, 8, 9, 13, 14, and 15. Everything else in that row will be replace with 0's.

I have tried using the IF function with a nested LOOKUP function but can't seem to get it to work correctly.

Any ideas?