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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
916
Eric W
Eric W
J
  • Question Question
COUNTIFS Hours and minutes in range
Replies
3
Views
909
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,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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