Find Row and Column Header using the specified value in a matrix

  • Hello,
    I'm stuck with a lookup where I want to find the column and row header from a matrix. The matrix presents distance "From" a node (specified as column headers) "To" another nodes (specified as row headers). I'm trying to create a ranking system here where i can formulate the shortest routes. I've attached a file and wrote the notes in there.


    Btw, linear programming or pivot is not an option here. I have to come up with good old formulas.

  • Re: Find Row and Column Header using the specified value in a matrix


    oh it does, thanks a lot. great help.
    will let you know if i face any problems putting the formula in the main sheet.


    thanks a lot (bows)

  • Re: Find Row and Column Header using the specified value in a matrix


    It worked beautifully. Thanks a lot.
    But I'm facing a practical issue with the real data. Some of the central nodes are appearing multiple times in the ranking. Since those "Central Nodes" in "From" or "To" columns gives the shortest possible routes than the remote ones.


    Is there any way to edit the formula so that once a column header appeared in the ranking list, it won't repeat again?
    For example, the calculation shows first four shortest routes are From XX to ABC, WW to ABC, YY to ABC and ZZ to JKL. I can use one route only once. Is it possible to omit ABC from the calculation and get ZZ to JKL right after XX to ABC?


    hope I'm not being too vague or greedy.


    Thanks again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!