Return multiple column headers based on row match and values between two numbers

Occasional Visitor

I have been looking for days and can find similar examples but nothing that pinpoints why I cannot write this formula.  I have data on 'Mileage!', on sheet 1 I have a dashboard with a dropdown in cell B3.
When I select the dropdown I want a formula to match B3 to 'Mileage!B5:B13 and return the column headers (headers are Mileage!F4:I4 and data is F5:I13) for different catagories (to eliminate zero I need .01-50 miles, 51-100 miles, 101-150 miles, 151-200miles and 201-300 miles).

I thought I had the formula and have done the evaluate formula, but it gets stuck after I get the false, false, true, etc step.  I know it has to be done as an array so I will be able to return multiple techs per catagory. Can anyone assist me in finding the correct formula to pull this info?  I am stumped!

1 Reply


Since I do not know what version of Excel you are using, I have no way of knowing whether this solution is appropriate.

= LET(
  miles,  XLOOKUP(Site, Table1[[Site ]], mileage),
  Array,  CHOOSE({1;2;3}, tech, "", miles),
  Nearby, FILTER(Array, miles<=50, "None"),
  TRANSPOSE(Nearby) )