Finding Values in a Table

[New2You]

This may be a basic question...maybe not.

I am trying to build a cost estimator for travel between various airports. My row names define the airport somebody might depart from. Column names define the airport they are going to. The cell that matches up with both the departure row value and arrival column value is the estimated airfare.

Said another way: If Cell A4 has a value of BOS (for Logan International in Boston) and Cell G1 has a value of LHR (London Heathrow), then cell G4 contains the value of the estimated airfare from BOS to LHR.

I need a formula that can determine this estimated airfare based on the values that are defined as departure locations and arrival locations between 31 different airports.

Complicating things are the fact that these departure and arrival values are themselves derived from conditional and lookup formulas referring to different sheets. So they aren't manually entered.

I'm thinking I need to use MATCH and combine it with some INDIRECT functions but I'm not making any headway.

Any thoughts? Thanks in advance!

-New2You

 

[Snakehips]

New2You,

This is based on Departures A2 to A32 - Destinations B1 to AF1

You have your somehow chosen Departure in say cell A35 and your chosen Destination in A36. To get the required cost in A37 enter the following formula....


=OFFSET(A1,MATCH(A35,A2:A32,0),MATCH(A36,B1:AF1,0))

You change the cells and ranges to suit the reality of your data if different.
NB the A1 equivalent will always be the top left intersect of your first data row and column.
Use data validation from list of airports in the cells where you select the Departure and Destination.

Let me know if that helps.

 

[New2You]

Brilliant!
Exactly what I was looking to do...and it makes perfect sense now.
Thanks a ton Snakehips. Now I can move on with my project!

Best regards,
New2You