0
votes

I am having problem with coming up a formula to lookup the minimum value of a particular product at a particular date (Sheet 2, Column C), and returning the name of the shop in (Sheet 2, Column D).

Sheet2

Sheet2

The sample data set is shown below: Sheet1

Sheet1

I need a formula (for each of column C and D) that would allow me to copy down the row, and gives me the lowest value of a certain product at certain date.

My initial thought for Column D is an array formula with INDEX, MATCH and MINIFS. However, I am not sure the formula for Column C. I was thinking about VLOOKUP but not sure how I should go with selecting the appropriate array of price of a given product.

Thank you in advance

1
Could you normalize your data to 4 columns - Product; Shop; Date: Value? They don't have to be in that order but would be much easier to analyze.Mark Fitzgerald
Unfortunately, the data is set up in this way to record the price of each product from different shops through timeZak
You should still normalize your data as suggested by Mark, you can then easily create a PivotTable in the exact format you have above and also do any other analysis and reporting that you want.Michael

1 Answers

2
votes

Lowest value should work this way:

=MIN(IF(($I$1:$L$1=$B2)*($H$3:$H$13=$A2),$I$3:$L$13))

Where: Check picture for data references

enter image description here

Here is solution to return shop in D column:

=INDEX($I$1:$L$26,2,MATCH(B2&C2,INDEX($I$1:$L$1&OFFSET($I$1:$L$1,MATCH($A2,$H$1:$H$26,0)-1,),,),0))