INDEX MATCH based on 5 criteria, including a date range.

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

Please Login or Register  to view this content.

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):

Please Login or Register  to view this content.

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

Hi benishiryo,

Thanks a lot for your quick reply, simply brilliant! Thank you very much, the 5 criteria should never appear more than once, so I used the SUMIFS function and it works great, way more efficient than what I was trying to do.

Thanks again,
Ant

Fix it bro....

Pls check the files


Azumi