Returning multiple values from a range

ACNstrat

New Member
Joined
Dec 19, 2005
Messages
3
Hi everyone,

The purpose of this question is to return values for an interview survey

The values that each of the 30 respondants could say for the 8 questions asked would be 1,2,3,4,5 to reflect their opinions. I want to be able to easily return everyone's name who said 1, everyone who said 2, everyone who said 3, etc. for each question of the survey.

I have tried tinkering with VLOOKUP and MATCH/INDEX to do this, but am not getting anywhere.

Does anyone have any insight as to what the proper protocol is?
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
ACNstrat said:
Hi everyone,

The purpose of this question is to return values for an interview survey

The values that each of the 30 respondants could say for the 8 questions asked would be 1,2,3,4,5 to reflect their opinions. I want to be able to easily return everyone's name who said 1, everyone who said 2, everyone who said 3, etc. for each question of the survey.

I have tried tinkering with VLOOKUP and MATCH/INDEX to do this, but am not getting anywhere.

Does anyone have any insight as to what the proper protocol is?

Are you trying to produce a table of results per question?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
ACNstrat said:
Yes, I think so, Aladin.

Just for Question 1 (Q1)...
aaCondSubListExtract ACNstrat.xls
ABCDEFGHIJKLM
131120
2NameQ1Q2Q3Q4Q112345
3eva1223evadanjonbrian 
4dan2131damon  karin 
5brian4321greg    
6jon3433     
7karin4313 
8damon1525  
9greg1121  
10
Sheet1


H1, copied across:

=COUNTIF($B$3:$B$9,"="&H2)

H2:L2 houses the answer set.

H3:

=IF(ROWS(H$3:H3)<=H$1,INDEX($A$3:$A$9,SMALL(IF($B$3:$B$9=H$2,ROW($A$3:$A$9)-ROW($A$3)+1),ROWS(H$3:H3))),"")

which is confirmed with control+shift+enter then copied across and down.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,017
Messages
5,569,640
Members
412,284
Latest member
Daibear
Top