Table function

[Fernando Lopes Jr]

I need a fucntion that is able to read a table cross the data from the rows
and colums and return a value. The table is 9x9 in the colums and rows I have
the name of nine islands the data is the distance between then. I need a
function that I say island 2 and island 3 and it returns the distance from
the table.

Thanks for the help

 

[Jacob Skaria]

Hi Fernando

Post the data layout and show us an example..

If this post helps click Yes

 

[Bernd P]

Hello,

Define your table range as name D.

If A1 holds name of island 1 and A2 name of island 2 enter into B1,
for example:
=INDEX(D,MATCH(A1,INDEX(D,1),0),MATCH(A2,INDEX(D,,1))

[untested]

Regards,
Bernd

 

[Gord Dibben]

With Island1 to Island9 in A2:A10 and Island_1 to Island_9 in B1:J1

Distances in B2:J10

Select A1:J10

Insert>Name>Create. Checkmark in Top Row and Left Column

Now in a cell enter =Island3 Island_7 to return the distance between those
two Islands.


Gord Dibben MS Excel MVP


On Fri, 16 Oct 2009 02:24:01 -0700, Fernando Lopes Jr <Fernando Lopes

 

[John]

Hi Fernando
If your table is like this: you can use this formula
=INDEX(B2:E5,MATCH(A8,A2:A5,0),MATCH(B8,B1:E1,0))
I'm using A8 & B8 cell to set my criteria "Island 3 with Island 2"
Change the range to your needs.
A B C D E etc...
1 island 1 island 2 island 3 island 4
2 island 1 5 25 40 55
3 island 2 15 20 22 45
4 island 3 12 14 18 22
5 Island 4 4 14 12 16

HTH
John