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


--
SamUK
------------------------------------------------------------------------
SamUK's Profile: http://www.excelforum.com/member.php...o&userid=16709
View this thread: http://www.excelforum.com/showthread...hreadid=319143

You can use INDEX and Match to return the price. There's an example he

http://www.contextures.com/xlFunctio...ml#IndexMatch3

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




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Sam

Please don't fall in love with me unless you are Samantha

Discover the amazing world of SUMPRODUCT

=SUMPRODUCT((A1:A100="Dubai")*(B1:B100="Portland") *(C1:C100))

In plain English sum column C if in the coreesponding cell of column A
there is "Dubai" and in the corresponding cell of column B there is
Portland. As there is only one value that satisfies the conditions,
you get your answer

Find out all that you need to know about SUMPRODUCT at:

http://www.excel-vba.com


On Sun, 21 Nov 2004 07:57:52 -0600, 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

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=Sheet1 !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


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=319143

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

If you are going to use the formulas provided, I presume you will need to be
able to explain how and why they work. The SUMPRODUCT formula given is not
documented in this way anywhere in MS (AFAIK), so you might have a problem.

Pierre gives some explanation at
http://www.excel-vba.com/e-formula-sumproduct.htm, but this tells you it
works and how to use it, not why it works. You might want to check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html and quote these 2 pages
as references in your submission.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pierre Leclerc" wrote in message
...
Hi Sam

Please don't fall in love with me unless you are Samantha

Discover the amazing world of SUMPRODUCT

=SUMPRODUCT((A1:A100="Dubai")*(B1:B100="Portland") *(C1:C100))

In plain English sum column C if in the coreesponding cell of column A
there is "Dubai" and in the corresponding cell of column B there is
Portland. As there is only one value that satisfies the conditions,
you get your answer

Find out all that you need to know about SUMPRODUCT at:

http://www.excel-vba.com


On Sun, 21 Nov 2004 07:57:52 -0600, 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

Hi mon amie,

Pierre Leclerc Wrote:
You cannot really use INDEX/MATCH with 2 criterias,...

Yes you can. I'd suggest you give my example a try. :)

...use SUMPRODUCT as
explained in a previous message.

SUMPRODUCT is fine if, as in this case, the value to return is a
numerical one. But in other cases, if the value to be returned is a
text value, you would need INDEX/MATCH. And you can either use the
array formula I offered or, if an array formula doesn't appeal to you,
go the round about route using concatenation.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=319143

Domenic

Intersting your formula but I have abandonned SUM as array formula
because of the Ctrl/Shift/Enter users react poorly to it. That is why
I use SUMPRODUCT.

Do you have a way of INDEX/MATCHING with two criterias without
CTRL/SHIFT/ENTER

Thanks


On Sun, 21 Nov 2004 09:50:54 -0600, Domenic
wrote:


Hi mon amie,

Pierre Leclerc Wrote:
You cannot really use INDEX/MATCH with 2 criterias,...

Yes you can. I'd suggest you give my example a try. :)

...use SUMPRODUCT as
explained in a previous message.

SUMPRODUCT is fine if, as in this case, the value to return is a
numerical one. But in other cases, if the value to be returned is a
text value, you would need INDEX/MATCH. And you can either use the
array formula I offered or, if an array formula doesn't appeal to you,
go the round about route using concatenation.

Pierre Leclerc
www.excel-vba.com
1-800-501-6760

Pierre Leclerc Wrote:
Do you have a way of INDEX/MATCHING with two criterias without
CTRL/SHIFT/ENTER

Sure! Have a look at Aladin's contribution here...

http://www.mrexcel.com/board2/viewtopic.php?t=108386&highlight=concatenate#javas cript:void(0);


Personally, I don't like this approach. I prefer the INDEX/MATCH array
formula. :)

Au revoir!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=319143