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?!?

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD]Respondent:[/TD]
[TD]Definitely[/TD]
[TD]Likely[/TD]
[TD]Not Sure[/TD]
[TD]Unlikely[/TD]
[TD]Definitely Not[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]URN 1[/TD]
[TD]Definitely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URN 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Sure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URN 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Unlikely[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URN 4[/TD]
[TD]Definitely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URN 5[/TD]
[TD][/TD]
[TD]Likely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

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

 

[FormR]

Does anyone know how I upload it to here for you to look at what I mean?

Hi, can't you just post sample tables directly in thread like you did in the first post?

 

[NJS1982]

OK, tried an image uploader thing. This is the first tab, showing the summary tab.:

https://ibb.co/MZtVkZp

This is the second tab, showing the SMData (source and response data) tab:

https://ibb.co/6tSqC2h

 

[NJS1982]

Hi, can't you just post sample tables directly in thread like you did in the first post?

Thanks, but less simple tables. I'll give it a go though!! Thanks for your perseverance here!

 

[NJS1982]

OK, here goes. So this is the first summary tab (a simplified version of it anyway), with the required formula being required in the blank cell:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]URN[/TD]
[TD]Q13: Will you capture INCOME GENERATED?[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]56789[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]78901[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]89012[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90123[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Then the source data sheet, where I am dumping all of the data, looks like this (again, simplified):

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]URN[/TD]
[TD]Income generated - Definitely[/TD]
[TD]Income generated - Likely[/TD]
[TD]Income generated - Not Sure[/TD]
[TD]Income generated - Unlikely[/TD]
[TD]Income generated - Definitely Not[/TD]
[TD]Income generated - N/A[/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD]Definitely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]56789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Definitely Not[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90123[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not sure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]89012[/TD]
[TD][/TD]
[TD]Likely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]Definitely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]78901[/TD]
[TD][/TD]
[TD]Likely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not sure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD]Definitely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, the response to the question how likely is respondent 34567 to collect information on income generation, I want to display (in the first table) the response 'Definitely'.
78901 would be 'Likely', 12345 would be 'Not sure' and so on...

 

[mole999]

Tried Dropbox but don't think it works.
Excel online only shares to people in my work network.
Argh!

trusted cloud services that don't require registration for those assisting is the preferred way if necessary

 

[FormR]

Hi, sorry - got busy all of a sudden!

You can try this. Note that the text in cell B1 and C1 etc needs to partially match the text in row 1 of the data source sheet. i.e. can't include Q13 - and the other preamble.

Excel 2013/2016
	A	B	C
1	URN	INCOME GENERATED	another question
2	12345	Not sure	likely
3	23456	Definitely	Definitely Not
4	34567	Definitely	not sure
5	45678	N/A	Definitely
6	56789	Definitely Not	not sure
7	67890	Definitely	likely
8	78901	Likely	likely
9	89012	Likely	n/a
10	90123	Not sure	unlikey
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(2,1/(ISNUMBER(SEARCH(B$1,Sheet2!$B$1:$L$1))*(INDEX(Sheet2!$B:$L,MATCH($A2,Sheet2!$A:$A,0),0)<>"")),INDEX(Sheet2!$B:$L,MATCH($A2,Sheet2!$A:$A,0),0))

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	URN	Income generated - Definitely	Income generated - Likely	Income generated - Not Sure	Income generated - Unlikely	Income generated - Definitely Not	Income generated - N/A	another question - Definitely	another question - Likely	another question - Not Sure	another question - Unlikely	another question - Definitely Not	another question - N/A
2	67890	Definitely							likely
3	56789					Definitely Not				not sure
4	90123			Not sure							unlikey
5	45678						N/A	Definitely
6	89012		Likely										n/a
7	34567	Definitely								not sure
8	78901		Likely						likely
9	12345			Not sure					likely
10	23456	Definitely										Definitely Not
Sheet2

 

[NJS1982]

Wow, that kind of works, for some!! Impressive. I have copied across to the other questions too.

I am however getting some errors on just some lines, which is odd. For example, I have URN 2017004414 that is returning #N/A# across all cells.
The actual response is 'Not Likely' so the initial two cells (Definitely and Likely) are blank.

The full formula I am using is this:
=LOOKUP(2,1/(ISNUMBER(SEARCH($P$1,'Survey Monkey Data'!$A$2:$CL$2))*(INDEX('Survey Monkey Data'!$A:$CL,MATCH(A21,'Survey Monkey Data'!$A:$A,0),0)<>"")),INDEX('Survey Monkey Data'!$A:$CL,MATCH(A21,'Survey Monkey Data'!$A:$A,0),0))

Cell P1 takes into account your point about the question; it contains a cell that simple says Income generated* with that wildcard to allow for the different scenarios, I think.

 

[FormR]

I have URN 2017004414 that is returning #N/A# across all cells.

Try a simple formula to test if the URN numbers really matches between the two sheets - change the cells to the ones that contain the URN 2017004414.

=A21='Survey Monkey Data'!A1

Does the formula return TRUE?

says Income generated* with that wildcard to allow for the different scenarios

FWIW - you don't need to include a wildcard character - it won't hurt though and is not the problem.

 

[NJS1982]

Hi, A21='Survey Monkey Data'!A21 is returning TRUE, yes.

The URN is matching for definite on the Survey Monkey sheet, because I have a whole host of other columns with more simpler VLOOKUP formulas that work fine, so not a missing URN / additional space / number formatting issue I don't think?

 

[NJS1982]

No, ignore me, sorry! Just realised that I had mis-entered the URN to the one below, so when I was cross-checking the responses, they were not the same.
Turns out the URN I referred to actually has no response to that section (it is possible, by design, to by-pass this question) hence the error!
I'll fix with an ISERROR formula somewhere.
Many, many thanks for you help, massively appreciated!!