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.

 

[FormR]

Hi, maybe you can see if you can adapt this to your set-up.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Hubs>
2	Site	H1	H2	H3	H4	H5	H6	H7	H8	H9		Drop Down	H3
3	S1	16	98	95	5	86	51	69	34	7		>= Criteria	80
4	S2	3	15	63	23	1	98	91	66	31		<= Criteria	200
5	S3	85	68	88	21	75	199	74	15	150		Helper	3
6	S4	18	73	46	80	100	20	35	49	48		Results	S1
7	S5	25	98	77	33	98	10	95	27	37			S3
8	S6	54	41	7	91	83	87	16	60	3			S7
9	S7	46	69	81	26	21	21	54	93	62
10	S8	16	98	23	6	64	67	89	44	10
Sheet1

Cell Formulas
Range	Formula
M5	=COUNTIFS(INDEX($B$3:$J$10,0,MATCH($M$2,$B$2:$J$2,0)),">="&M3,INDEX($B$3:$J$10,0,MATCH($M$2,$B$2:$J$2,0)),"<="&M4)
M6	{=IF(ROWS(M$6:M6)>$M$5,"",INDEX($A$3:$A$10,SMALL(IF(INDEX($B$3:$J$10,0,MATCH($M$2,$B$2:$J$2,0))>=$M$3,IF(INDEX($B$3:$J$10,0,MATCH($M$2,$B$2:$J$2,0))<=$M$4,ROW($A$3:$A$10)-ROW($A$3)+1)),ROWS(M$6:M6))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[spm0r1n]

FormR, thanks for response. Ithought I had relied yesterday, but I do not see them.

The first part of the response works well and the results changes as I change the values for the range. Here is what I have as a formula;
=COUNTIFS(INDEX(sites!$R$2:$BF$1445,0,MATCH($B$3,sites!$R$1:$BF$1,0)),">="&C3,INDEX(sites!$R$2:$BF$1445,0,MATCH($B$3,sites!$R$1:$BF$1,0)),"<="&D3)

the second part however is not working. I am entering the formula correctly as I do get the {} brackets. I keep getting #N/A?
This is the formula I am using
=IF(ROWS(F$4:F4)>$F$3,"",INDEX(sites!A2:A1445,SMALL(IF(INDEX(sites!$R$2:$BF$2,0,MATCH($B$3,sites!$R$2:$BF$2,0))>=$C$3,IF(INDEX(sites!$R$2:$BF$1445,0,MATCH($B$3,sites!$R$2:$BF$2,0))<=$D$3,ROW(sites!$A$2:$A$1446)-ROW(sites!$A$2)+1)),ROWS(F$4:F4))))
Here are the actual cells I am using mapped to yours in the example
M2 =B3
M3=C3
M4=D3
M5=F3
M6=F4

Thanks

 

[FormR]

This is the formula I am using

Hi, give this a try, you were quite close - I'll let you see if you can spot the differences (assuming it works)

=IF(ROWS(F$4:F4)>$F$3,"",INDEX(Sites!$A$2:$A$1445,SMALL(IF(INDEX(Sites!$R$1:$BF$1445,0,MATCH($B$3,Sites!$R$1:$BF$1,0))>=$C$3,IF(INDEX(Sites!$R$2:$BF$1445,0,MATCH($B$3,Sites!$R$1:$BF$1,0))<=$D$3,ROW(Sites!$A$2:$A$1446)-ROW(Sites!$A$2)+1)),ROWS(F$4:F4))))

 

[spm0r1n]

So I am now getting " A value is not available to the formula or function.

I assume I need to make sure all fields in the Sites worksheet have a valid numerical /text reference?

I did catch the errors though, thanks again

 

[FormR]

So I am now getting " A value is not available to the formula or function.

Hi, where is that message coming from? Are you using Excel?

 

[spm0r1n]

Error is on the cell I am entering formula, F4. Using excel 2016 all contained in same spreadsheet. Testing formulas on separate worksheet.

 

[spm0r1n]

It seems to be working but as you can see there are 32 sites for the range selected, but only one site shows up?

	Hub	Range StartStarting
	12110
					TDC0190

<colgroup><col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="width:48pt" span="4" width="64"> </colgroup><tbody>
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 130"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[TD="colspan: 2"]RangeEnd[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]80[/TD]

[TD="align: right"]32[/TD]

</tbody>

 

[spm0r1n]

Man, I tell you. Some times it is the simplest things that get you. I had not copied the formula downward. DOH!!

Thanks again for the assistance.

 

[FormR]

I had not copied the formula downward. DOH!!

Ooops - glad you figured it out.