URGENT lookup Problem

[Chompa]

Hi
I am in university working on my final year project and I am having some
trouble. I have a "to from" table on a worksheet. i.e. places along the the
top and down the left hand side. (diagonal line through the middle top left
to bottom right where value is zero)

On a separate sheet I want to use a drop down menu to select firstly the
column by place then the row by place and for the value where these coincide
to be returned.

Is this possible andd if so what formulae should i be using?

Thanks

 

[Stefi]

=INDEX(Munka2!A1:G7,MATCH(G2,Munka2!A:A,0),MATCH(H2,Munka2!1:1,0))
where
Munka2 - sheet name containing "to from" table
A1:G7 - sample range of "to from" table
G2 - cell for drop down list, vertical
H2 - cell for drop down list, horizontal


Regards,
Stefi

„Chompa†ezt írta:

 

[Bob Phillips]

The formula would be something like

=INDEX(Sheet1!$A$1:$M$20,MATCH(selected_row,Sheet1!$A$1:$A$20,0),
MATCH(selected_column,Sheet1!$A$1:$M$1,0))

 

[Chompa]

Thank you both for the help, but i am still having problems...

I will try and explain further:
The first worksheet where I want the drop down menus and the retrieved
values is called sheet1. The worksheet with the to from data is called Ref.
The table on the worksheet looks like:
A B C D E DO
1 AB AL BA BB ... YO (118 entries in all)
2 AB
3 AL
4 BA
5 BB
....
119 YO
(118 entries in all)

There are over 13,000 numbers in the cells. e.g. distance between AB and AL
is 521.

I tried using the suggest formulae but received a value error.
Could it be the drop down menus iam using. (created via: data->
validation->Allow:List etc.)

If you can other any further help it would be greatly appreciated.

Thank you
Chris

 

[Stefi]

=INDEX(Ref!A1O119,MATCH(A1,Ref!A:A,0),MATCH(B1,Ref!1:1,0))

where
Ref - sheet name containing "to from" table
A1O119 - range of "to from" table
A1 - cell for drop down list, vertical
B1 - cell for drop down list, horizontal


Regards,
Stefi

„Chompa†ezt írta:

 

[Chompa]

Stefi
Thank you for the prompt reply. Problem solved.

Thank you so much

Chris

 

[Bob Phillips]

Isn't that what I said?