Lookup last criteria

[Thomas Kuriakose]

Respected Sirs,

We have a table of products in lookup tab, the material numbers and three years discount values.

There is an input tab which picks up values from the lookup tab.
a) Based on the product type (C3 : P3) it should pick the right material number
b) Based on the Print date (B1), it should lookup up Table1 for the product type,material number and lookup the corresponding yearly discount based on the date Table 2.

Is there a better formula for a) =VLOOKUP(C3,Table1[[Product Type]:[Material]],2,FALSE)

I tried =SUMPRODUCT((Table1[Material]=C4)*(Table2[From]>=Input!B1)*(Table2[To]<=Input!B1)*(Table1[[#All],[2019]:[2021]])) to get the discount but the year parameter I am not able to get.

Kindly guide on how to get the required result as displayed in the attached file.

Thank you very much for your guidance and support always.

with regards,
thomas

 

[bosco_yip]

Try,

C6, copied across right to P6 :

=INDEX(Table1[[2019]:[2021]],MATCH(C3,Table1[[Product Type]:[Product Type]],0),MATCH(1,INDEX((Table2[[From]:[From]]<=Input!$B$1)*(Table2[[To]:[To]]>=Input!$B$1),0),0))

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution provided. This works perfectly.

Much appreciated.

with regards,
thomas