Complex Lookup

Hello,

I'm new to this forum and quite a beginner when it comes to look ups in excel... I hope that someone can help me finding a solution

I have created a file with several worksheets, containing price lists on them. On a new worksheet I've created an overview with pick lists to define the, size, nbr of colours and volume of the materials needed.

What I want to do is enter a function which:
- looks at the size selected, nbr of colours selected and volume selected of the material
- then goes to the 1st price list and searches the price according to these selections

My price lists are set up as follows:
- first column: size of material
- second column: number of colours (1-6) for each size
- 1st row: volumes

I hope that this is clear enough, let me know if you need more detials or an example.

Thanks for any help I can get!!

Kiwi

Welcome to the board

could you post a sample of your data so we can have an idea of your layout?

Cheers

Hi,

Here's an example of what I'm doing.

Thanks.

Using the INDEX and MATCH formulas, you can construct the lookup to return the proper value. Esentially you create a range with all of your values and then you calculate how many rows down the thickness starts, add how many rows down the colors start, then go over as many rows as the quantity is.

=INDEX('Supplier 1'!C12:K52,MATCH(D4,'Supplier 1'!A12:A52,0)+MATCH(E4,'Supplier 1'!B12:B17,0)-1,MATCH(F4,'Supplier 1'!C11:K11,0))