LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied

[Mr Wiffy]

I am having a problem with one of my spreadsheets for my GNVQ I.T.
coursework. Please help!

My spreadsheet is for a boat hire company. On one table is a list of the
boats, times to hire the boats and the cost to hire the boat for that amount
of time.
eg.
Boat One | 2 hours | £25 (per head)
Boat One | 3 hours | £35 (per head)
Boat Two | 2 hours | £30 (per head) etc. etc.

On another sheet, there is a table that lists the customers that have hired
the boats, which boat they hired, how many people are on the boat and the
time it has been hired for.
eg.
Mr R Brown | Boat One | 3 hours | 4 people | (Price) etc. etc.
I want to establish a VLOOKUP in a 'Price' box on the second sheet that will
look for the combination of 'Boat One' and '3 hours' on the FIRST sheet,
then see what the price is and multiply it by how many people are on the
boat.

I tried:
=VLOOKUP(B15:C15 [the cells that Boat One and 3 hours were in on the second
sheet], 'First Sheet'!A10:C40 [the first three columns on the first sheet,
that displayed the boat name, time and price], 3 [which would give the price
as it was in the third column], FALSE)*D4

But this did not work. I think it is something to do with the B15:C15 at the
start of the formula, as when I took one out and left it as B15 the formula
worked fine but obviously did not return the desired result.

I am desperate for some help here and would be grateful for ANY pointers you
could give me!

Thanks in advance,
Imogen

 

[Bart Snel]

One way would be to create an extra column on both sheets and concatenate
Boats and hours and gthen use the VLookup function.
I agree though there must be more elegant solutions.

Bart Snel

 

[smonczka]

I'm not sure if this is elegant but it works...

First you need to create an actual table for the first sheet with your
data in it...

A B C D
1 lookup Boat Hours Per Head
2 11 1 1 $25.00
3 12 1 2 $26.00
4 21 2 1 $27.00
5 22 2 2 $28.00
6 31 3 1 $29.00
7 32 3 2 $30.00


You can see that I have inserted a column called "lookup". This is
a combination of the Boat number and the Hours. You can do this by
hand or by inserting the function "=CONCATENATE(C3,D3)" in A2 the
copy and past for the rest of the rows in that column. You can hide
this column after you are done if you don't want anyone to see it.

Define A27 as a range. In this case I used "range01"

On your second sheet make something like this...

H I J K L M
5 Name Boat Hours (value) Heads Price
6 Brown 1 2 12 4 $104.00

I have again inserted a column called "(value)" in column K which
is also a combination of the boat and the hours. Again I used the same
formula "=CONCATENATE(I6,J6)". Then in the Price column I used the
formula "=VLOOKUP(K6,range01,4)*L6". This takes the combination of
the Boat/Hours that is now in column K and looks it up in our range
"range01" and then multiplies it by the number of heads in L6.

You can then hide column K so that it doesn't show up on your sheet
and confuse anyone.

Hope that helps you out.

Steve