VLOOKUP for multiple values

[jhb019]

Hi

I'm doing a project in college and I really need some help.
I have to make a system for a crazy golf company on excel.
Basically, I have one worksheet with a bookings form on it and another with a food and drink ordering form.
The ordering form works by you selecting a customer ID on a list. You then select an item of food or drink from a list and this then brings up the price in the next cell which is taken from another worksheet via VLOOKUP. You then type in the quantity and this brings up the total in the final cell.

I then want to be able to put the amount of money spent on food and drink by a certain booking ID in a single cell on the bookings worksheet. I did this using VLOOKUP.

However, because you can only order one item of food at a time, if one booking ID orders more than one item of food, the total amount ordered by that booking ID is not displayed on the bookings worksheet. It only displays the first amount VLOOKUP finds on the food and drinks worksheet.

I want to know if there is a formula that is similar to VLOOKUP, but would allow me to retrieve multiple values from a different worksheet, add them together and then display the answer in a single cell.

Thanks
jhb019

 

[Sam_D_Ben]

Hey J,

Wolcome to MrExcel.

It is possible. if you don't mind why don't you drop some sample data here.

Muz.

 

[jhb019]

Here is some images of my problem:
Bookings Form:

Food and Drink Ordering Form:

As you can see, in the extras field it says £1.40 for Booking ID 5 rather than £5.30 which is should be. As you can see, VLOOKUP is returning the first value it finds.

The VLOOKUP formula I'm using is: =VLOOKUP(A9,'Food and Drink Ordering'!A5:E6,5,FALSE)

If I change the formula from FALSE to TRUE the value changes from £1.40 to £3.90 which is the second value on the food ordering worksheet.

Thanks
jhb019

 

[danzon]

Use SUMIF instead of Vlookup

=SUMIF('Food and Drink Ordering'!A:A,A9,'Food and Drink Ordering'!E:E)

 

[jhb019]

That worked!!!

Thanks so much
jhb019