If and match function

[ehardway123]

Hey all, I am having trouble with this formula. I created two teir drop down, one of the product and the dropdown of customers that have that product. I am trying to get the net revenue of the customer under that product for a month. The thing is that the customer is not unique under that product, a customer can have multiple products. I tried doing an hlookup but was unable to get the revenue since it isn't unique and looks up only the first value it see. So next I added a key that added the product&customer. I created two pivot tables now. One of products with customers underneath it with totals by month and another that has the key (customers&products) to get the net revenue of the month of the product I have selected. How do I get the net revenue of January of a customer of a specific product I choose from dropdown 1? I have tried doing this formula but it doesn't work. =IF(A12=A10,(INDEX(rev,MATCH(A12,Sheet5!A1:A234,0),2,""))) , where a12= a9&a10 (a9=dropdown of customer, a10=dropdown of product). I was trying to get the product that I choose to match with the key to get the revenue for month of jan.

 

[ehardway123]

I figured out a way to do it but in the APR and AUG it is giving me an N/A. I used this forumula: =HLOOKUP(N7,rev,MATCH($M$9,Sheet5!$A$1:$A$234,0)) , N7 is Jan and M9 =a12 (customers&products), it did it for all the other months except APR and Aug. APR forumula : =HLOOKUP(Q7,rev,MATCH($M$9,Sheet5!$A$1:$A$234,0)) . It should be $150 but instead it is giving me an N/A. Any idea why? The rest of the Months, beside AUG, are working properly.