Index, Match,or List??

[spm0r1n]

I have an spreadsheet with 40+ columns that are support hubs, and 1400+ rows of support sites. I have distance data for all contained in a matrix format.

I want to be able to use a drop down menu to select one of the support hubs, and then have list/match to select all entries within the hubs column that match certain criteria, i.e <= 80, =>200

once the matches are found, I need the row headers( Supported Sites) to be copied to another worksheet in either row or column.

Will a variation of the following work? Both of theses are working fine.
This formula looks through the distance of the sites and matches the smallest next smallest,etc, and populates the column header.

<colgroup><col span="6" width="84"></colgroup><tbody>
[TD="class: xl64, width: 84"]=INDEX($X$1:$BM$1,MATCH(SMALL($X18:$BM18,1),$X18:$BM18,0))
[/TD]

</tbody>

=INDEX($X$1:$BM$1,MATCH(SMALL($X13:$BM13,2),$X13:$BM13,0))
=INDEX($X$1:$BM$1,MATCH(SMALL($X13:$BM13,3),$X13:$BM13,0))

This formula just populates the actual mileage
=SMALL($X13:$BM13,1)
=SMALL($X13:$BM13,2)
=SMALL($X13:$BM13,2)


Thanks.

 

[Mike Szczesny]

I created a dummy data sample based on FormR's response #2 . [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]I used a sumproduct formula to give the count
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =SUMPRODUCT(($B$3:$G$10>=$J$3)*($B$3:$G$10<=$J$4)*($B$2:$G$2=$J$2))[/TD]
[/TR]
</tbody>[/TABLE]

=IFERROR(SMALL(IF($B$2:$G$2=$J$2,IF($B$3:$G$10>=$J$3,IF($B$3:$G$10<=$J$4,$B$3:$G$10))),ROWS($J$1:J1))," ") seems to work. Again you need to use Cntr+Shift+Enter


[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Hubs[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Site[/TD]
[TD]x[/TD]
[TD]xx[/TD]
[TD]xxx[/TD]
[TD]xxxx[/TD]
[TD]xxxxx[/TD]
[TD]xxxxxx[/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]>=[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]<=[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]42[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]48[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]33[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[/TR]
</tbody>[/TABLE]