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

 

[Peo Sjoblom]

You can use an array formula, however the design has some flaws, remove all
text from the parts you need to calculate with or you won't be able to do
any calculations, so in the price column use only numbers and in the number
of people use only numbers, the header should be explanatory enough

for the price

=INDEX(Price_range, MATCH(1,(Boat_Range=Boat_number)*(Time_Range=Time),0))

it needs to be entered with ctrl + shift & enter

do the same for number of people

=INDEX(People_Number_range,
MATCH(1,(Boat_Range=Boat_number)*(Time_Range=Time),0))

then multiply the two

 

[Charyn]

Are you required to use lookups in the the formula?