Returning a value in a matrix

[Stuck14]

Hi

I have two worksheets.

Worksheet one is a matrix, customer, pack size on the vertical, and date (365 days) on the horizontal. Within that matrix for each customer, bag size and date a rate is given for a discount.

Worksheet two has also customer, pack size and date.

What i am trying to do is look up the three elements on worksheet two (customer, pack size and date) to return the value in the matrix.

Any ideas?

Cheers

Stuck

[RudiS]

Hi,

Welcome to the forum.

If you concatenate Customer and Pack Size into a third column, then use =INDEX(array,MATCH(3rd col),MATCH(date))
Hope that makes sense

[Stuck14]

Ohh thank you, i will give that a go

[Tony Valko]

Can you post a SMALL sample file so we can see how your data is structured?

We don't need a gigantic file with 100's of columns and 1000's of rows of data!

5 rows and 5 columns worth of data will be plenty!

Make sure you tell/show us what result you expect.

[Stuck14]

I have just put a little something together that might explain what i am doing.

Obviously i have a huge amount of real data, and this is just for a simplified example.

In the first tab "discount" i have the variable rates of product sales discount values. This is entered manually depending on the customers account.

In the second tab "data" I need to match the value of the discount rate buy customer, pack size, and date.

For example.

Customer A, ordered pack size 1 on the 05/07/13. I want to put a formula in that picks up the discount rate for that customer, for that pack size on that particular day. In this case £1.02. I can then simply multiply that by the quantity.

For background.

The majority of the data is simply a download report from the system. The discount rates is manually kept as its highly variable.

Help appreciated. Thanks all.

[Tony Valko]

One way...

Entered in G2 and copied down:

=SUMIFS(INDEX(Discount!D$2:L$5,0,MATCH(D2,Discount!D$1:L$1,0)),Discount!B$2:B$5,B2,Discount!C$2:C$5,C2)

[Stuck14]

Outstanding Tony, thank you

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.