How to use INDEX, MATCH and MIN functions to pull data from a matrix

[Nur Azriati Mat]

Hi and good day everyone,

I would like to know how to use INDEX and MATCH functions to find min value in a row from a matrix.

Herein, I attached my workbook.
The formula, D20=INDEX($C$4:$M$14,MATCH(C19,$B$4:$B$14,0),MATCH(C20,$C$3:$M$3,0))

Based on this formula, I got the answer and yes the answer give me the minimum value of objective function (min total distance).

But, when I would like start the route by Node 1 and must select the most closer node from Node 1. In this case, next closer node from Node 1 is Node 11.

I don't know how to formulate it, in order Node 11 being selected.

I hope you can understand my problem.

Thank you.

 

[Peter Bartholomew]

Are your node labels always going to be numbers from 1 upward? If so, you do not need MATCH to return the node index, the label is the index.

The nearest node will be given by a formula of the form
= MATCH( MIN( INDEX( distance.table, start.node, ) ),
INDEX( distance.table, start.node, ), 0 )

The fun and games starts when you want to eliminate previously visited nodes. Then, instead of applying the formula directly to the 'distance.table', you will need to apply it to
= IF( available?, distance.table, 100 )
where 'available' is a Boolean array that tests for the occurrence of candidate end nodes for the leg in the list to date.

 

[Nur Azriati Mat]

Thanks sir for your help.

For your information, in this problem, the route must always start with Node 1.

Can you explain more for your given formula,
= MATCH( MIN( INDEX( distance.table, start.node, ) ),
INDEX( distance.table, start.node, ), 0 )

The formula should be paste at C19 or D20.

Thanks.

 

[Peter Bartholomew]

The formula works by using INDEX (with the column parameter missing) to select a row from the distances table. MIN will then give the minimum value for each row. MATCH is used to locate the minimum by index within the row.

The named formulas are particularly important to the solution since they hold the formulae that are used to evaluated a row in terms of its predecessors.

 

[Peter Bartholomew]

Names ​

Refers to (formula)

nodes.visited: This formula identifies the range containing previously visited nodes [it is also possible to pick the range out using $ to create mixed references]​

= INDEX(leg.start,1) : INDEX(leg.start,order-1)

visited?: This is a Boolean row array that determines whether each of the candidate end nodes has been visited before.​

= N(ISNUMBER(MATCH( node.end, nodes.visited, 0 )))

current.distances: This returns the array of distances from the leg start node to each of the candidate end nodes.​

= IF( visited?, 100, INDEX( distance.table, current.node, ) )

From there
= MIN( current.distances )
gives the distance to the next node and
= MATCH( MIN( current.distances ), current.distances, 0 )
determines the node index.

 

[Nur Azriati Mat]

Thanks sir for your help.
I will study about this.