How to create a Dropdown list that returns values that depends on another cell

How do I create a dropdown list that returns values
DEPENDING on what I enter in another cell.

I've attached my excel sample file so that you can better understand my problem. Thanks in advance. This forums has been very helpful.

Thanks for all the help so far DonkeyOte.
I've read the tutorial you recommended, and I decided to use the 'OFFSET' function. I've managed to make a dropdown list that depends on the value of another cell.

However, I'm having problem creating a dependent dropdown list that depends on the values entered on two cells instead of just one value.

To illustrate, let's say there are 3 columns in an invoice. The first one is a dropdown list for the product where you can choose which product to enter. The next column is a dependent dropdown list that returns the product type available based on the value entered on the product column. Column 3 is the size column. The size available depends on the product and the type entered. So, what formula can I use here to create a list that depends on two values?

I've attached my sample workbook to better illustrate.
Thanks a lot!

DonkeyOte

Thanks for the reply and thanks for the tip
I tried putting your formula but unfortunately it didn't work. I tried using your formula as is and put it in the Size column Data validation in the Invoice workbook. I also tried naming the ranges before putting the formula in the Data Validation. However, all didn't work. I didn't know what could be wrong. If you don't mind, can you put the formula in my sample invoice workbook? I could be missing some things since I didn't really understand Excel.

Thanks a lot for your time and effort! I've attached the updated sample invoice workbook with the correct spelling.

Hello DonkeyOte,

I've followed your instructions step by step but I still couln't make it work.
In the Size column, only value "_Size" appear no matter what Product and Type I choose.

FYI, I changed the " , " in the formula to " ; " since I'm in a different region than you are. I was wondering if that could be a problem?

Also, did the formula work when you tried it?

Thanks

Nevermind, I finally got it.
I went through everything over and over and finally found what I missed.
I missed one " = " in the Data validation column.

One question, do you have to name ranges/references before they are to be entered in the Data Validation formula ? Because sometimes I get a message that "you cannot make references". Something like that. So, I just named some of my ranges, and then they worked. I'm not sure about it though.

Also, can you explain the logic of the last formula you gave me? I'm lost on what it actually does. I understand the first part where it compares two values and extract the third value that match those two criterias. However, I don't really understand the use of SUMProduct in this situation.

Btw, Thanks a lot Donkey Ote, I appreciate your help.

I am also working on something of the same. My figured out how to do a drop down list. That was easy. No I would like to be able to use the drop down list in one column, click on the item and then have the coorespondong invoice number appear. I created a drop down list for the invoices already, so I want to be able to Use the client's name to get to the invoice number without having to look it up.