How to use lookup function with multiple criterias?

[surfol]

Hey guys,

I'm new to the forum and it's been a very long time since I used Excel. I've forgotten all the formulas that I learned during college. And that was for Excel 2003; now I'm using Excel 2007. So, hopefully you experts can help me out.

So here's the problem. I need to set up an invoice in Excel but I don't know what formula to use. I'll first describe my invoice and then I'll tell you the problem.

It's a basic invoice where you have customer's info and product's details and prices. So, when I choose a customer's name from a list in a cell, the address and phone of that customer will automatically appear. I don't have a problem at all with this part. And then there's the product details section where you enter a product type, size, color, price per unit, and total price. The price per unit also depends on the customer entered. Each customer has different pricing and I used a discount system for each customer. We basically have a universal price list for our products, but we have different discount percentage for each customer.

My invoice will look something like this:

Bob
123 Street
San Francisco, CA

--------------------------------------------------------------------------
Product Type | Size | Color | Quantity | Price per Unit | Total Price
--------------------------------------------------------------------------
ABC Spring Bed | 160 | 2 | 300 | 600|
ABC Spring Bed | 120 | 3 | 250 | 750|
ABC Spring Bed Set| 120 | 1 | 500 | 500|
XXX Spring Bed | 140 | 2 | 400 | 800|
XXX Spring Bed Set| 140 | 1 | 800 | 800|


My plan is to use 'LIST' so that I can choose a product type and product size from a list and I'll manually enter the quantity. But as soon as I enter those info, I'd like the price per unit and the total price to automatically appear. And here's the problem.

I have a data table which has information on the product type, size, and corresponding price per unit which I plan to use for the invoice. You can see the data table below. In the price per unit cell in the invoice, I put a VlookUp formula that looks up the data table below and give the corresponding price. The problem is, for each product there are many different sizes and each has different prices. And Vlookup can only lookup 1 criteria/lookup value. What do I have to write in the formula so that it can FIRST lookup a product type from the invoice and THEN lookup the size so that it can return the correct price. For example, I want to look up 'ABC Spring Bed' with size 90 from the data table. Obviously I can't just use vlookup since there are multiple 'ABC Spring Bed' entries. I know I can just combine the Product Type and size into 1 column so it reads something like 'ABC SPring Bed 90', 'ABC Spring Bed 120' and so on. But that's not what I want to do since that wouldn't look neat on the invoice.

So do I have to use other formula other than vlookup? Can anyone help me out.

I also have a data table which lists the customers' discount percentage, so each customer has different price per unit. How do I incorporate this as well in the invoice? The price that's written on the 'price per unit' on the invoice will be the universal price unit minus the discount.

Thanks a bunch!


-----------------------------------------------
Product Type | Size | Universal Price per unit
-----------------------------------------------
ABC Spring Bed | 90 | 120|
ABC Spring Bed | 120 | 250|
ABC Spring Bed | 160 | 300|
ABC Spring Bed Set | 120 | 500|
XXX Spring Bed | 140 | 400|
XXX SPring Bed | 180 | 750|
and so on..

----------------------------------------------
Customer | Discount percentage |
-----------------------------------------------
ABC | 45%|
BDF | 50|
XXY | 51|

[DonkeyOte]

It will be a lot easier for all concerned if you post a sample workbook based on your above post... and also outline some desired results etc so people can double check they've captured & replicated fully your logic.

[NBVC]

To extract the cost you can use Sumproduct

e.g.

=Sumproduct(--(A1:A100=X1),--(B1:B100=Y1),C1:C100)

wherwe X1 and Y1 hold the variables to be found in A1:A100 and B1:B100, respectively and then pull from C1:C100

[DonkeyOte]

NBVC, given use of XL2007 I would suggest SUMIFS in preference to SUMPRODUCT.

[NBVC]

NBVC, given use of XL2007 I would suggest SUMIFS in preference to SUMPRODUCT.

In that case,

=Sumifs(C1:C100,A1:A100,X1,B1:B100,Y1)

but note this is not backwards compatible

[surfol]

thanks a lot, that was really helpful.
I decided to use sumproduct since I assume that should be backward compatible.

Any suggestion on how to incorporate the customer's discount?
I was thinking to use

= (1 - vlookup('customer's name cell', 'customer's discount data table', col no. , false)) * sumproduct(--(array1=X1), --(array2=Y1), array3)

Also, anyone here know good tutorial sites that teach the business applications of Excel like this one?

thanks

[NBVC]

If that works for you, then why not use it.

[surfol]

Hey guys,

I thought I solved the problem, but there were problems with the sumproduct formula. Sometimes It works, sometimes it returns a value of zero. I attached an excel file that illustrated my problems, and hopefully someone can take a look at it and help me out. There are 3 problems in total. Thanks a lot guys!

[DonkeyOte]

Note trailing spaces in B11:B12 ... ie "LJ Spring " rather than "LJ Spring"... if this is an issue that can't be corrected in the cells directly (ie inherited) then use TRIM in the SUMPRODUCT, eg:

Please Login or Register  to view this content.

added trim to J range for good measure (so same logic applied to both criteria and test range)

For your "price per unit" query - add conditions and alter final range, eg:

Please Login or Register  to view this content.

I would suggest reading through Bob Phillips' white paper on the SUMPRODUCT function in which the above approaches are outlined in depth - see SUMPRODUCT link in my sig,.

[surfol]

Thanks a lot DOnkeyote. You just solved two of my problems. But I still have no idea how to solve problem no.3/Invoice no.3 in my workbook. It has a different kind of table format. Thanks for the link, I'll do more reading once I got home.

[DonkeyOte]

Re: problem 3, either of:

Please Login or Register  to view this content.

Note again use of TRIM to normalise the values.