Jul 19 2021 01:44 PM
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!
Jul 19 2021 03:23 PM
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) )