Help using two different values in a lookup table

[shadestreet]

Help using the Lookup Function
I need to search for a value based on two different criteria.

I have only used VLookup or HLookup for 1 criteria, and would like t
know how to use a combination of the two. I am sure there are multipl
approaches, but I wanted to know the simplest method.

Basically, I have created a spreadsheet to calculate distances whic
are dependant on "origin" and "destination". There are 8 possibl
locations, any of them can be the origin or the destination. I made
distance matrix on a different sheet that has the travel distance fo
each of the 64 combinations.

On my my first sheet (titled "Distance") I use two adjacent cells, on
titled "origin", and the other "Destination". The user will select th
origin and destination from the Validation list, and I want the thir
adjacent cell to lookup and return the distance from the matrix on th
second page (titled "DMatrix").

Last month I posted basically the same question and Frank was kind t
answer, but I couldn't get it work (used a combination of Index an
matching). Can someone explain how to achieve this?

Thank

 

[Peo Sjoblom]

=INDEX(A1:S50,MATCH(Origin_cell,A1:A50,0),MATCH(Destination_cell,A1:S1,0))

will return the cell in the table where the intersection is, so if it looks
up H1 and A30 then the
returned value will be from H30

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom