VLOOUP and INDEX I think?!

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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:DefinitelyLikelyNot SureUnlikelyDefinitely NotN/A
URN 1Definitely
URN 2Not Sure
URN 3Unlikely
URN 4Definitely
URN 5Likely

<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"
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
assuming A1 is your start point, how about =TRIM(B2&C2&D2&E2&F2&G2) in H
 
Upvote 0
Hi, thanks for the response. Hadn't thought of TRIM; seems to work if looking and working on one sheet, however...all of these responses are on another sheet, and I am trying to pull through via a VLOOKUP to another worksheet, like a summary.

So a lot of these kind of formulas, where '23' is the questions response:
=IF(A3="","",VLOOKUP(A3,'SMData'!$A$1:$CN$1000,23,0))

Is there a way to add TRIM into this formula, so column index numbered as 23 here is 'trimmed' to return just the right, single one (as in effect, the entire question covers six columns...)?
 
Upvote 0
why not add the extra column on that sheet and do that work, then all you have to do is a index match between the results sheet and the data. just my first thought
 
Upvote 0
Yes, would seem logical. But the process I have is to dump the downloaded data onto one sheet, for it to then simply populate onto another when the URN is added. If I add steps into the area where I dump all of the data, then it just complicates it for colleagues who don't have any kind of Excel skills, to be fair! I just want them to copy and paste the values of an entire (source date) sheet onto this workbook (the SMDAta tab), not add in certain columns on the SMData tab to work out additional formulas there.
 
Upvote 0
"What is the response to URN 1, for question 1"

Hi, its not clear where the question number comes into play, but based on your sample table you could try something like:


Excel 2013/2016
ABCDEFGHIJ
1Respondent:DefinitelyLikelyNot SureUnlikelyDefinitely NotN/AURN 3Unlikely
2URN 1Definitely
3URN 2Not Sure
4URN 3Unlikely
5URN 4Definitely
6URN 5Likely
Sheet30
Cell Formulas
RangeFormula
J1=LOOKUP(REPT("z",255),INDEX(B:G,MATCH(I1,A:A,0),0))
 
Upvote 0
Thanks; what is the "z" and the 255 in this formula represent?

My questions (so B1:G1) are actually:

Income generated - Definitely
Income generated - Likely
Income generated - Not sure
Income generated - Unlikely
Income generated - Definitely Not
Income generated - N/A
 
Last edited:
Upvote 0
what is the "z" and the 255 in this formula represent?

It creates a large string to guarantee lookup value is larger than the values in lookup array, essentially forcing the lookup() function to return the last non-blank cell it finds.
 
Last edited:
Upvote 0
It is kind of hard to explain it properly, so I have created a sample spreadsheet.
Does anyone know how I upload it to here for you to look at what I mean?
 
Upvote 0
Tried Dropbox but don't think it works.
Excel online only shares to people in my work network.
Argh!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top