I currently have an index formula in place that returns all results matching multiple criteria: raw material, period (falling between the start and end date) and price.
The final obstacle I have is trying to also return the column header name where the price is found based on the other criteria mentioned.
The formula I have that is working finds the supplier/s and port/s.
The formula for ‘Supplier’ is:
=IFERROR(INDEX(Table1,SMALL(IF(COUNTIF($B$2,Table1[Raw material])*COUNTIF($C$2,”>=”&Table1[Period start])*COUNTIF($C$2,”<“&Table1[Period end])*COUNTIF($R$2,Table1[[BEP]:[Thornton]]),ROW(Table1)-MIN(ROW(Table1))+1),ROW(A1)),2),””)
The final thing I need is the ‘Mill’. In an ideal world, in the table that I am retrieving data from, I would include an extra column titled ‘Mill’ and list the various mills in that column and simply use the same formula as the above and change the column number at the end. Unfortunately this is not possible, what I have instead are 5 additional columns at the end for the 5 mills, their data includes is a total of the price + their individual haulage charge.
The header names from the table I am retrieving data from are:
Date; Supplier; Raw material; Store; Period start; Period end; Price; BEP; Clearwell; Finmere; Seamer; Thornton (final 5 are the mills as mentioned).
Apologies in advance if my question is still not clear.
By: Josh Goldsbrough