Index Match to return second matching value from row 12

K

kelle

Board Regular
Joined
Apr 1, 2015
Messages
93
I'm working with a survey dataset that needs to be aggregated for analysis purposes. Right now I'm looking to identify all instances where different respondents answered a question with the same numeric answer from a multiple choice list, the number of respondents who answered with the same answer, and then a list of the numerical values that they answered with.

So basically, right now I have a data table that identifies which numerical value each respondent (total of 9 respondents) responded with for each question, with the numerical values ranging from 1-42.

Right now I have a table that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]BK
[/TD]
[TD]BL
[/TD]
[TD]BM
[/TD]
[TD]BN
[/TD]
[TD]BO
[/TD]
[TD]BP
[/TD]
[TD]BQ
[/TD]
[TD]BR
[/TD]
[TD]BS
[/TD]
[TD]BT
[/TD]
[TD]BU
[/TD]
[TD]BV
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Total
[/TD]
[TD]Respondents in Support
[/TD]
[TD](formula in this column looks at data table in S2:BH12 to identify how many of the numerical values had X
[listed in column BK] supporters)
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]9
[/TD]
[TD]Respondents
[/TD]
[TD]0
[/TD]
[TD]=IF($BM3<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK3,$S$12:$BH$12,0))) -- answer is 0, as BM3 is less than BN2
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8
[/TD]
[TD]Respondents
[/TD]
[TD]1
[/TD]
[TD]=IF($BM5<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK5,$S$12:$BH$12,0)))<strike></strike> -- answer is 19 (the matching header from the first column in the dataset that states "8" as the subtotal)
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7
[/TD]
[TD]Respondents
[/TD]
[TD]2
[/TD]
[TD]=IF($BM5<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK5,$S$12:$BH$12,0)))​ -- answer is 18 (the matching header from the first column in the dataset that states "7" as the subtotal)

[/TD]
[TD]HOW DO I GET THE COLUMN HEADER OF THE SECOND INSTANCE OF 7 in subtotal of the dataset?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6
[/TD]
[TD]Respondents
[/TD]
[TD]1
[/TD]
[TD]=IF($BM6<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK6,$S$12:$BH$12,0)))​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5
[/TD]
[TD]Respondents
[/TD]
[TD]1
[/TD]
[TD]=IF($BM7<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK7,$S$12:$BH$12,0)))​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4
[/TD]
[TD]Respondents
[/TD]
[TD]3
[/TD]
[TD]=IF($BM8<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK8,$S$12:$BH$12,0)))​
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SECOND INSTANCE OF 4 in subtotal of the dataset?​
<strike></strike>
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE THIRD INSTANCE OF 4 in subtotal of the dataset?​
​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3
[/TD]
[TD]Respondents
[/TD]
[TD]6
[/TD]
[TD]=IF($BM9<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK9,$S$12:$BH$12,0)))​
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SECOND INSTANCE OF 3 in subtotal of the dataset?​
<strike></strike>
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE THIRD INSTANCE OF 3 in subtotal of the dataset?​
​
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE FOURTH INSTANCE OF 3 in subtotal of the dataset?​
<strike></strike>
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE FIFTH INSTANCE OF 3 in subtotal of the dataset?​
​
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SIXTH INSTANCE OF 3 in subtotal of the dataset?​
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2
[/TD]
[TD]Respondents
[/TD]
[TD]6
[/TD]
[TD]=IF($BM10<BN$2,"",INDEX($S$2:$BH$2,MATCH($BK10,$S$12:$BH$12,0)))
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SECOND INSTANCE OF 2 in subtotal of the dataset?​
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE THIRD INSTANCE OF 2 in subtotal of the dataset?​
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE FOURTH INSTANCE OF 2 in subtotal of the dataset?​
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE FIFTH INSTANCE OF 2 in subtotal of the dataset?​
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>
[/TD]
[TD]
HOW DO I GET THE COLUMN HEADER OF THE SIXTH INSTANCE OF 2 in subtotal of the dataset?​
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Of course, there's like 4 separate posts on how to do this on ExcelJet, but it seems to be down for me no matter what computer I visit it from...
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

S
  • Question Question
Lookup If Blank Or only have certain value
2
Replies
14
Views
2K
sandy666
sandy666
A
  • Question Question
Formula for conditional formating - NOT AS EASY AS IT SEEMS
Replies
3
Views
914
Eric W
Eric W
J
  • Question Question
COUNTIFS Hours and minutes in range
Replies
3
Views
902
CyrusTheVirus
CyrusTheVirus
E
  • Question Question
VBA autofilter 1004 No cells were found
Replies
5
Views
1K
Fluff
Fluff
R
  • Question Question
MAXIF and return another column
2 3
Replies
21
Views
3K
Aryatect
Aryatect

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

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