In the attached on a copy of your sheet calle Sheet 1(2) is an awful but working formula which you need to check gives the right results. You can put this formula in B2 and copy down and across, but you will need to change some references within the formula to fit your data ranges. Try not to use entire columns in the references as it will take longer for the formulae to calculate.
There is an alternative solution on that sheet in terms of a pivot table which is based on your Table3 on the Distance sheet. You can add more data to Table3, change its size in both directions, then all you have to do is refresh the pivot table. It uses Power Query behind the scenes to create an invisible table which in turn feeds the pivot table.
The info in the pivot is arranged differently (vertically) with the To field being sorted smallest to largest Distance, but you can quickly filter the results (I've added a slicer as it's easier to use than the dropdown arrows).
|