How to Use array match function in a distance matrix

[muddit0]

There are 8 cities in the country of Eight, A, B, C, D, E, F, G and H. Mr. Z decides to visit each city in his car starting from A. His planned itinerary is
A-->B-->C-->D-->E-->F-->G-->H.
The distance between the cities is given in Table I


Table I
Distance
A B C D E F G H
A 0 200
B 200 0 350
C 350 0 500
D 500 0 250
E 250 0 850
F 850 0 1250
G 1250 0 150
H 150 0



Write a formula which takes the number of kms that Mr. Z has travelled from A as the input and displays the name of the city which is nearest to that point.

Mr. Z will enter the number of kms he has travelled from A in cell D30 and the name of the city nearest to the point will be displayed in E30[PHP][PHP][table="width: 500, class: grid"]

[simarui]

is this a homework assignment?

[muddit0]

No its not homework

[muddit0]

No its not homeworkScreen Shot 2018-03-21 at 12.01.36 AM.png

[simarui]

do you have flexibility in the layout / arrangement of the data? this is not really a very convenient format.

attached is probably how i would tackle this problem... create a cumulative distance column and then a midpoint column and match on a greater than basis.

[protonLeah]

title updated....

[muddit0]

Thank you for your reply, But I want to do it without making any new data columns.

[Skiptomylou]

You could try this, though it is not tested.

Please Login or Register  to view this content.

Would be easier if you could attach a workbook.

[muddit0]

Workbook is attached now, thanks.

[Phuocam]

Change table1 to:

A B C D E F G H
0 200 350 500 250 850 1250 150

Use array formula:

=INDEX($C$11:$J$11,MATCH(D30,SUBTOTAL(9,OFFSET($C$12,,,1,COLUMN(C11:J11)-COLUMN(B11))),1))

Enter with Ctrl+Shift+Enter.

[muddit0]

I want to do it without adding other column or changing the data structure

[Phuocam]

Array formula:

=INDEX($C$11:$J$11,MATCH(D30,0.5*SUBTOTAL(9,OFFSET($C$12,,,COLUMN(C11:J11)-COLUMN(B11),
COLUMN(C11:J11)-COLUMN(B11))),1))

[muddit0]

Its working by using this formula but i am not able to understand it completly
=index($c$11:$j$11,match(d30,0.5*subtotal(9,offset($c$11,,,column(c11:j11)-column(b11),column(c11:j11)-column(b11))),1))

Thank you very much for helping

[dominicb]

Cross-posted : https://www.mrexcel.com/forum/excel-...x-problem.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[Glenn Kennedy]

Doesn't the answer at Post 12 give the nearest city that Mr Z has ALREADY been to... not the nearest one, ahead or behind him??

[Glenn Kennedy]

This array formula gives the closest city (in front or behind):

=INDEX($C$11:$J$11,MATCH(MIN(ABS(0.5*SUBTOTAL(9,OFFSET($C$12,,,COLUMN(C12:J12)-COLUMN(B12),COLUMN(C12:J12)-COLUMN(B12)))-D30)),ABS(0.5*SUBTOTAL(9,OFFSET($C$12,,,COLUMN(C12:J12)-COLUMN(B12),COLUMN(C12:J12)-COLUMN(B12)))-D30),0))

[shg]

Please Login or Register  to view this content.

[muddit0]

Thank you very much for providing the solution.

[Glenn Kennedy]

Who are you talking to????

[muddit0]

This array formula gives the closest city (in front or behind):

=INDEX($C$11:$J$11,MATCH(MIN(ABS(0.5*SUBTOTAL(9,OFFSET($C$12,,,COLUMN(C12:J12)-COLUMN(B12),COLUMN(C12:J12)-COLUMN(B12)))-D30)),ABS(0.5*SUBTOTAL(9,OFFSET($C$12,,,COLUMN(C12:J12)-COLUMN(B12),COLUMN(C12:J12)-COLUMN(B12)))-D30),0))

Thank you very much Mr. Glenn Kennedy, It works.

[Glenn Kennedy]

Make sure that you thank Phuocam, too. He did the hard part!!!

[muddit0]

Array formula:

=INDEX($C$11:$J$11,MATCH(D30,0.5*SUBTOTAL(9,OFFSET($C$12,,,COLUMN(C11:J11)-COLUMN(B11),
COLUMN(C11:J11)-COLUMN(B11))),1))

Thank you very much for providing the solution.

[leelnich]

Another ARRAY FORMULA* in E30:

Please Login or Register  to view this content.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.