Lookup for multiple criteria and duplicate conditions

[Graemea]

Hi,


I have data arranged in A1:E5 and want to extract values from B3:E5 based on criteria given by the Product Code in A3:A5, the date in
B1:E1 and the Data Type in B2:E2.


The extract forumla references these conditions in cells G1:G3. e.g G1 = AAA, G2 = 31/08/2017 and G3 = Percentile.


The formula I have, which broadly works, is:


{=INDEX($B$3:$E$5,MATCH(1,IF($A$3:$A$5=G1,1),0),MATCH(1,IF($B$1:$E$1=G2,IF($B$2:$E$2=G3,1)),0))}


The wrinkle is that there are two instances in which a column has the same dates in row 1 and same data types in row 2
i.e. C1 and E1 have 31/08/2017 and C2 and E2 both have "Percentile"


As constructed my INDEX MATCH formula above can't distinguish columns C and E when extracting data.


One way to distinguish them is that the second column with these characteristics for a given date (E in my example)
always has a column immediately to the left with the text "Cost of Sales" in its row 2 field (so in D2 in my example).


Can someone please suggest how to alter my formula, above, to accommodate this adjustment.


Thanks!

 

[Graemea]

Perhaps a simpler way to frame the question would be how could I amend my INDEX MATCH formula to return the result that's in the 2nd column that meets the 3 criteria.

Does anyone know how to do this?

Thanks!