Match/Index not pulling correct values

[spm0r1n]

I had some great help last week relating to this post; Index, Match,or List??

For the most part everything is working except that the formula I am trying to use to pull out the actual distances in the cell is not working?

The spreadsheet is huge bu I can show you the formulas that are working

This formula works and provides the value for H3

{=COUNTIFS(INDEX('Site Info'!$V$2:$BV$1445,0,MATCH($B$3,'Site Info'!$V$1:$BV$1,0)),">="&E3,INDEX('Site Info'!$V$2:$BV$1445,0,MATCH($B$3,'Site Info'!$V$1:$BV$1,0)),"<="&F3)}

This formula works and pulls the text values from column "c" that match the range selected with cells E3 and F3

{=IF(ROWS(B$8:B8)>$H$3,"",INDEX('Site Info'!$C$2:$C$1445,SMALL(IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))>=$E$3,IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))<=$F$3,ROW('Site Info'!$C$2:$C$1446)-ROW('Site Info'!$C$2)+1)),ROWS(B$8:B8))))}

This formula works and pulls the text values from column "g" that match the range selected with cells E3 and F3

{=IF(ROWS(C$8:C8)>$H$3,"",INDEX('Site Info'!$g$2:$g$1445,SMALL(IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))>=$E$3,IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))<=$F$3,ROW('Site Info'!$g$2:$g$1446)-ROW('Site Info'!$g$2)+1)),ROWS(C$8:C8))))}


This formula is inputting values that would be correct if matching the values pulled from column(s) in the previous examples, but the values are not in line with the range works defined and pulls the text values from the correct column,


{=IF(ROWS(H$8:H8)>$H$3,"",INDEX('Site Info'!$B$2:$B$1445,SMALL(IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))>=$E$3,IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))<=$F$3,ROW('Site Info'!$B$2:$B$1446)-ROW('Site Info'!$B$2)+1)),ROWS(H$8:H8))))}

The range defined is 0 - 30 Mi and I am getting values outside of the range?

 

[spm0r1n]

The more I look at this, I suspect it is the first formula that is not working?

 

[FormR]

Hi, it's not clear - to me at least - what isn't working and what is.

How about trying to post some sample data and tell us in words what the expected results should be.

 

[spm0r1n]

A B C D E F G H
1 Hub Range Count
2 Nj01 0 10 3
3
4
5 Title -------------------------------------------------
6
7 State Site ID Address-------------- Distance
8 NJ 11010 245 my ave 0
9 NJ 11234 123 any street 3
10NJ 10456 567 your cr 7
11NJ 12221 356 nowhere dr 10
12

Best I can do for sample data;

Worksheet formula for count
=COUNTIFS(INDEX('Site Info'!$V$2:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0)),">="&E3,INDEX('Site Info'!$V$2:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0)),"<="&F3)

Array Formulas

for state reference column is text
{=IF(ROWS(A$8:A8)>=$H$3,"",INDEX('Site Info'!$J$2:$J$1445,SMALL(IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))>=$E$3,IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))<=$F$3,ROW('Site Info'!$J$2:$J$1446)-ROW('Site Info'!$J$2)+1)),ROWS(A$8:A8))))}

for Site ID reference column is text
{=IF(ROWS(B$8:B8)=$H$3,"",INDEX('Site Info'!$C$2:$C$1445,SMALL(IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))>=$E$3,IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))<=$F$3,ROW('Site Info'!$C$2:$C$1446)-ROW('Site Info'!$C$2)+1)),ROWS(B$8:B8))))}

for address reference column is text
{=IF(ROWS(C$8:C8)>$H$3,"",INDEX('Site Info'!$G$2:$G$1445,SMALL(IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))>=$E$3,IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))<=$F$3,ROW('Site Info'!$G$2:$G$1446)-ROW('Site Info'!$G$2)+1)),ROWS(C$8:C8))))}

For Distance reference column is number

{=IF(ROWS(H$8:H8)>$H$3,"",INDEX('Site Info'!$B$2:$B$1445,SMALL(IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))>=$E$3,IF(INDEX('Site Info'!$V$1:$BQ$1445,0,MATCH($B$3,'Site Info'!$V$1:$BQ$1,0))<=$F$3,ROW('Site Info'!$B$2:$B$1446)-ROW('Site Info'!$B$2)+1)),ROWS(H$8:H8))))}

The sample values I provided are indicative of the actual. For the hub in question there are actually 4 sites with the mileages indicated, but it seems to not count or pull data from row 2 which is the site with o mileage
All of the other information is correct.

Data on the reference worksheet 'Site Info' is sorted by Hub and then mileage.


Thanks Steve

 

[spm0r1n]

Forgot to add, the value for the hub in B3 is selected via a data validation list, so the index and match seems to be working?

 

[FormR]

Hi, sorry, it's still not clear what the actual problem is.

This formula is inputting values that would be correct if matching the values pulled from column(s) in the previous examples, but the values are not in line with the range works defined and pulls the text values from the correct column

What does this mean?