Hi

You cannot really use INDEX/MATCH with 2 criterias, use SUMPRODUCT as
explained in a previous message. When I discoverd SUMPRODUCT, I
dropped the array formula and the Shift/Control/Enter thing.

Discover it at:

http://www.excel-vba.com/index-agent.htm


On Sun, 21 Nov 2004 08:46:07 -0600, Domenic
wrote:


Assuming that your lookup table in your "Prices" worksheet is contained
in A2:C10, enter the following array formula that needs to be confirmed
with CONTROL+SHIFT+ENTER...

=INDEX(Prices!C2:C10,MATCH(1,(Prices!A2:A10=Sheet 1!A2)*(Prices!B2:B10=Sheet1!B2),0))

..where Sheet1!A2 contains the departure city of interest and
Sheet1!B2 contains the arrival city of interest.

Hope this helps!

SamUK Wrote:
Hiya everyone,

I'm having some trouble getting a VLOOKUP to work in a spreadsheet. I'm
designing a spreadsheet for school, which should automatically calculate
how much it costs to fly to places etc.

Anyway, as I said, I'm having trouble with a VLOOKUP. I want my
spreadsheet to calculate how much it costs to fly from City A to City
B. I have another sheet called "Prices" which contains 3 columns:
Departure City, Arrival City and Price. For example, the first row has
"East Midlands, Dubai, £300"

So far, so good.

However, I also have a flight from East Midlands to London Gatwick and
this is where the problem arises. You see, my VLOOKUP only looks into
the first column (departure airport) so if, for example, I had chosen
East Midlands on my first sheet, it would choose the price attached to
the East Midlands to Dubai flight. I can't get it to check out the
second column (arrival airport), so it just inserts the first one,
whether it's London or Dubai.

I would *really* fall in love with anyone who helps me, 'specially
since this is due in tomorrow and is worth 30% of my final grade! I
know this account gives me a semi-literate appearance, and I apologize
if I'm not clear, so just ask if you need anything clarifying.

Many thanks,
Sam

Pierre Leclerc
http://www.excel-vba.com