VLOOUP and INDEX I think?!

[NJS1982]

Hi all, been a while! I'm creating another spreadsheet and wondered if anyone can help me out with it.

I have a survey that asks a question, with responses to the question being:
- Definitely
- Likely
- Not Sure
- Unlikely
- Definitely Not
- N/A

The downloaded sheet displays the answers in six columns; if the respondent selects 'Definitely' for example, 'Definitely' is populated in column A, with columns B, C, D E and F being blank. If they select 'Not Sure', columns A and B are blank, C contains 'Not Sure' and D, E and F are also blank. And so on....

Each line of the sheet is from one individual, whereby they are given a Unique Reference Number (a URN).

I am trying to pull together all responses, so on another tab (tab A) I have all of the URNs and am using VLOOKUP to extract individual responses.
I want tab A to have a column asking something like 'What was the response to question 10', for this cell to display which of the six responses listed above.

I believe I have to INDEX it somehow but can't work it out; can anyone please help?!?

Respondent:	Definitely	Likely	Not Sure	Unlikely	Definitely Not	N/A
URN 1	Definitely
URN 2			Not Sure
URN 3				Unlikely
URN 4	Definitely
URN 5		Likely

<tbody>
</tbody>

SO, hopefully that makes sense. I am wanting a formula to tell me:

"What is the response to URN 1, for question 1"

 

[FormR]

OK Great, thanks for letting us know