Listing Nearest to Furthest Distances by reference name from an array

M

Minty111

New Member
Joined
Nov 20, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
postcode-outcodes DCJ.xlsx
ABCDEFGHIJKLMNOPQRST
1AaronBenChrisDarleneEmmaFredHarriettImogenPostcodeClosestFurthest
2idpostcodelatitudelongitudeB90NG34NE25DA1E16NR32NR7CM1Example>CV3AaronBenEmmaImogenDarlene
31B3452.49641-1.78176.98699467.23337177.0118111.4868102.8162147.7919131.3371108.2077Ouput
42IP1052.018981.26999133.686596.809238.224160.1294563.6420837.937143.3083139.41967
53G255.86291-4.25616258.7406252.0909122.0045355.4971347.967335.882316.7066343.182
64GL1951.94616-2.2909736.97394107.473217.1408112.4756103.7455174.3554160.6833118.134
75L3653.41441-2.8417381.93906105.5267125.6624187.007178.6091200.7798181.5612180.1643
86NE3254.96779-1.48449178.0475143.85015.846014253.084246.9311215.8301197.5023236.5178
97L1253.43498-2.895884.32286108.0513125.3511189.5839181.1765203.3219184.0859182.7921
108NR2852.822431.397492138.370875.51502193.6905107.4987107.608426.9179312.5618884.27391
119SO2151.07538-1.3162494.02331137.7933274.89370.8169265.44297162.842156.722689.42612
1210CV352.39098-1.4781414.6817161.3129183.89597.0376588.4283135.3574119.622993.50113
Sheet1


Using a half-postcode as a reference, I would like to look up that postcode in column B and find the 5 lowest numbers in that row in the array F3:M12 and put them in order from lowest to largest. I would then like to use those numbers to reference the names in F1 to M1, so instead of outputting a list of numbers it would instead output the names effectively showing the 5 closest people to that postcode, example shown in O:T using CV3 as the reference postcode.
I already have formulas for figuring out the distances so that is not relevant, thanks for any help.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Is this what you want then?

23 11 20.xlsm
BEFGHIJKLMNOPQRST
1AaronBenChrisDarleneEmmaFredHarriettImogenPostcodeClosestFurthest
2postcodeB90NG34NE25DA1E16NR32NR7CM1Example>CV3AaronBenEmmaImogenDarlene
3B346.98699416367.23337141177.0117681111.486779102.8161797147.7919314131.337098108.2077317OuputCV3AaronBenEmmaImogenDarlene
4IP10133.686507396.80900283238.224110860.1294487363.6420811437.937102943.3083073639.41966564G2ChrisBenAaronHarriettFred
5G2258.7405758252.0908582122.0044532355.4970653347.9670337335.8820039316.7066191343.1819513
6GL1936.97393674107.4730452217.1407942112.475577103.7455124174.3554387160.683336118.1339572
7L3681.93906459105.5267135125.662408187.0070434178.6091203200.7798133181.5612431180.1642538
8NE32178.0474903143.85008925.846013836253.0840282246.9310664215.8300517197.502339236.5178044
9L1284.32286425108.0512521125.3510588189.5838912181.1765181203.32192184.0859178182.7921358
10NR28138.370784775.51501568193.6904972107.4986644107.608423326.9179263912.5618790484.27390635
11SO2194.02331083137.79334274.892953570.8169218565.44297234162.8420277156.722603289.42611529
12CV314.6817105461.31290254183.895028597.0376475388.4282966135.3573748119.622943493.50112824
Close
Cell Formulas
RangeFormula
P3:T4P3=TAKE(SORTBY(F$1:M$1,INDEX(F$3:M$12,MATCH(O3,B$3:B$12,0),0)),,5)
Dynamic array formulas.
 
Upvote 0
Solution
Yes, that works a treat. I'd not seen TAKE before. Thank you - answered.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
how to refer to an element in a named range (array/matrix)
Replies
2
Views
237
Pascalvrolijk
P
P
  • Locked
  • Question
VBA: how to refer to an element in a named range (array/matrix)
Replies
1
Views
188
Fluff
Fluff
A
  • Question
Find and Replace Value in List Named Range Reference
Replies
0
Views
309
arembr01
A
D
  • Question
vstack targeting indirect list of table names
Replies
0
Views
670
dsehguht
D
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
998
pawcoyote
P

Forum statistics

Threads
1,215,430
Messages
6,124,850
Members
449,194
Latest member
HellScout

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top