vlookup, hlookup and return value of intersecting cell

[willemeulen]

I want to lookup a value of intersecting cells, find rate for specific trip.
The rates are now summarized in a table by looking up first column and top
row the intersecting cells give the rate. how do I do this.

Column A (Departure)
Column B (Destination)
Column C (Rate)

When column C is complete i will select all cells to arrange it according to
rate of trip.

Thanks

W

 

[Mike H]

Hi,

This assumes a table in A1 - H13 and the 2 lookup values in J1 & K1.

=INDEX(A1:H13,MATCH(J1,A1:A13,0),MATCH(K1,A1:H1,0))

Mike

 

[willemeulen]

My formula looks as follows:

=INDEX(sheet1!$A$2:$Q$18,MATCH(A2,sheet1!$A$2:$A$18,0),MATCH(B2,sheet1!$A$2:$Q$2,0))

for some reason the value returned is not what it should be. the lookup
table is on sheet 1 and I am working on sheet 2. The lookup/index values are
letters like CHI etc and return number a value. should the lookup/index value
be ascending? Or What else could be the reason for returning wrong value?

 

[willemeulen]

found the mistake, got it solved.

thanks mike

My formula looks as follows:

=INDEX(sheet1!$A$2:$Q$18,MATCH(A2,sheet1!$A$2:$A$18,0),MATCH(B2,sheet1!$A$2:$Q$2,0))

for some reason the value returned is not what it should be. the lookup
table is on sheet 1 and I am working on sheet 2. The lookup/index values are
letters like CHI etc and return number a value. should the lookup/index value
be ascending? Or What else could be the reason for returning wrong value?