VLOOKUP help

[SamUK]

Hiya everyone,

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

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

So far, so good.

However, I also have a flight from East Midlands to London Gatwick an
this is where the problem arises. You see, my VLOOKUP only looks int
the first column (departure airport) so if, for example, I had chose
East Midlands on my first sheet, it would choose the price attached t
the East Midlands to Dubai flight. I can't get it to check out th
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, 'speciall
since this is due in tomorrow and is worth 30% of my final grade!
know this account gives me a semi-literate appearance, and I apologiz
if I'm not clear, so just ask if you need anything clarifying.

Many thanks,
Sa

 

[Debra Dalgleish]

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

http://www.contextures.com/xlFunctions03.html#IndexMatch3

 

[Pierre Leclerc]

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

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
(e-mail address removed)

 

[Domenic]

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!

 

[Pierre Leclerc]

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

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!

Pierre Leclerc
http://www.excel-vba.com
(e-mail address removed)

 

[Bob Phillips]

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)

 

[Domenic]

Hi mon amie,

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
numerical one. But in other cases, if the value to be returned is
text value, you would need INDEX/MATCH. And you can either use th
array formula I offered or, if an array formula doesn't appeal to you
go the round about route using concatenation

 

[Pierre Leclerc]

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

Hi mon amie,



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


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

 

[Domenic]

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#javascript:void(0)


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

Au revoir