Extract multiple values from table - input into single cell

[Joe Shaer]

Hello,

I hope someone can help me with this.

I have a table with 'route' 'mile' and 'number of lanes'.

The criteria is to use the route number along with a mile range.

From this, I would like to extract the number of lanes within this mile range.

Below is a sample of the result. I am also attaching the workbook.

Thank you
Joe

 

[vletm]

Joe Shaer
Didn't Route 15 begin from 0?

 

[Joe Shaer]

Joe Shaer
Didn't Route 15 begin from 0?

Yes. However, the idea is to be able to select mile range (begin and end mile as noted in column G) and it will extract the number of lanes found within that range.

 

[Peter Bartholomew]

Padding the solution out a bit with a 365 formula

= LET(
      routeCriterion, Table1[Route] = route,
      mileCriteria,   (Table1[Mile] >= beginMile) * (Table1[Mile] <= endMile),
      filteredLanes,  FILTER(Table1[Lanes], routeCriterion * mileCriteria),
      distinctLanes,  SORT(UNIQUE(filteredLanes)),
      TEXTJOIN(",", , distinctLanes)
   )

My aim is a gentle, readable flow of calculation.

 

[Joe Shaer]

Thank you for your inputs. I have figured it out:
TEXTJOIN(",",TRUE,FILTER(I17:I23,(H17:H23>C14)*(H17:H23<D14)))

Sometimes things are simpler than I tend to imagine. I love excel :D

 

[bosco_yip]

Thank you for your inputs. I have figured it out:
TEXTJOIN(",",TRUE,FILTER(I17:I23,(H17:H23>C14)*(H17:H23<D14)))

Sometimes things are simpler than I tend to imagine. I love excel :D

I think you should use,

In H5 enter formula:

=TEXTJOIN(",",TRUE,UNIQUE(FILTER(D3:D13,(B3:B13=H2)*(C3:C13>=H3)*(C3:C13<=H4))))

 

[Peter Bartholomew]

@bosco_yip
Well spotted. I hadn't noticed the missing condition, presumably replaced by a manual selection.

After all these years you have made me realise something about my own cognitive processes while reading Excel formulae. I clearly do not read direct cell references (I see they are references but do not attempt to decode them or extract any information of significance). It is exactly the opposite conclusion to that of a EuSpRIG paper I read that asserted that users fail to identify errors in defined names; they just assume them to be correct and get on with checking the next direct cell reference. That goes some way to explaining why I avoid direct cell referencing whilst the rest of the world does the opposite!