Help using two different values in a lookup table

S

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

lookup prblem. 2
How do I use vlookup with two lookup values? 4
Lookup in data table - too many arguments? 3
vlook up two column 4
Need Matrix Lookup Formula 2
Vlookup where table array is in text format 4
Use info in separate table file 2
Help using the Lookup Function 1

Top