Simple Index Formula

[Russell719]

Hi There:

Well obviously it's not a simple formula for me

Excel 2007

I am trying a simple Index or Array formula and have been trying all day to get this work to no avail. The data is freight costs for shipping different size containers (20' and 40') to different ports (Lyndhurst, Port Coquitlam and Seattle).

I will have the port name plugged into one cell and the container size plugged into another cell and want to pull the corresponding freight cost out into another cell.

The red text in the file are the data that I will plug in. The box below is where I would like the result.

Thanks

[ChemistB]

Sounds like something for SUMPRODUCT or Pivot Tables but you'd have to load it as an xls file for me to see (many on this forum do not have 2007)

[Russell719]

xls file enclosed.
Tks for pointing that out.

[ChemistB]

In A8;

Please Login or Register  to view this content.

Your problem is that B1 and C1 are numbers and B6 is formatted as text. You might want to fix that cell. Otherwise, you will get #N/A. Questions?

[Russell719]

Solved.
Thank you so much !

[Tirren]

Russell719,

I'd also encourage you to make a list of your ports and then use data validation to make a simple drop down list for choosing which port you are checking. This will help you prevent mistakes in the formula from typing the wrong name, and provide quick access vs having to type the entire name out.

You might also consider making a table and using VLOOKUP / HLOOKUP to do the same thing.