Hi all,

I would need a formula to retrieve a value (i.e. the Annual Unit Price) from a lookup table based on 5 criteria, i.e.:
- Country
- Product Code
- Service Level
- Service Type
- Start Date (if it falls between a date range)

Test.jpg

From the attached screenshot, my lookup table can be found in A5:G13. In columns I to M are the 'matching' values I need to find in the lookup table, and column N is the retrieved value.

I have managed the following so far:

1) In cell N3, I used the array formula below to retrieve the Annual Unit Price based on the 4 criteria:
- Country - cell I3
- Product Code - cell J3
- Service Level - cell K3
- Service Type - cell L3

{=INDEX($G$2:$G$13,MATCH($I$3&J3&K3&L3,$A$2:$A$13&$B$2:$B$13&C2:C13&D2:D13,0))}
2) In cell N4, I used the array formula below to retrieve the Annual Unit Price if the Start Date (cell M4) falls between the Start Date (column E) and End Date (column F):

{=INDEX($G$3:$G$13,MIN(IF(($M$4>=$E$3:$E$13)*($M$4<=$F$3:$F$13),MATCH(ROW($G$3:$G$13),ROW($G$3:$G$13)))))}
I have now problems combining 1 and 2 above to have a single formula to retrieve the Annual Unit Price based on the 5 criteria; cell N6 shows the value that the formula would need to retrieve. Anyone could help 'merging' both formulas?

PS: also, if no matching values can be retrieved from the lookup table, is it possible for the formula to return 0 instead of #N/A?

Thanks a lot,
Ant

Test.xlsx