vlookup, hlookup and return value of intersecting cell

W

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
 
M

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
 
W

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?
 
W

willemeulen

found the mistake, got it solved.

thanks mike

willemeulen said:
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?
 

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

Top