Social
Could not grab feed.
Who is Plum?
Based in Sydney, Australia, Plum Solutions has been providing professional financial modelling solutions since 2004.Read more...
Hi Danielle,
I’ve tried various formulas but couldn’t get the right answer. Can you help?
Imagine the below is in an Excel sheet. I'd like a formula to pick up the distance from Banting to Kuala Lumpur, for example. How can I do this?
Alor Gajah Banting Chenor Dabong Kuala Lumpur Pulau Kambing
Alor Gajah 0 150 270 350 120 650
Banting 150 0 125 223 42 550
Chenor 270 125 0 412 123 345
Dabong 350 223 412 0 521 751
Kuala Lumpur 120 42 123 521 0 345
Pulau Kambing 650 550 345 751 345 0
Regards,
Sap
Based in Sydney, Australia, Plum Solutions has been providing professional financial modelling solutions since 2004.
Hi Sap,
This is not really an array formula (see http://www.plumsolutions.com.au/articles/should-we-use-array-formulas-ev... for an example of an array formula)
There are a number of ways of doing this - one way would be to create a nested formula using a combination of an INDEX and a MATCH formula.
Assuming your table starts from cell A2, this should work:
=INDEX(A2:G8,MATCH(A12,A2:G2,0),MATCH(B12,A2:A8,0))
I hope this helps,
Danielle.
Submitted by danielle on Mon, 06/07/2009 - 17:00.
Hi Danielle,
Thank you.
The formula is perfect.
My model shall be completed soon.
Submitted by Anonymous on Mon, 06/07/2009 - 17:00.
Post new comment