Array Formula

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

Back to forum topics

Comments

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 - 18:00.

Hi Danielle,

Thank you.

The formula is perfect.
My model shall be completed soon.

Submitted by Anonymous on Mon, 06/07/2009 - 18:00.

Post new comment

Subject:

Comment: *

• Web page addresses and e-mail addresses turn into links automatically.
• Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd><img><h1><h2><h3><p><br><iframe>
• Lines and paragraphs break automatically.
• Images can be added to this post.

More information about formatting options

CAPTCHA

This question is for testing whether you are a human visitor and to prevent automated spam submissions.

What code is in the image?: *

Enter the characters shown in the image.