Nested formula help

[colans]

See attached file...
I have a list of contracts with a number of products and contract prices that change frequently so there are date ranges. I have another list of "chargebacks" that were issued based on the contract prices and dates. I want to validate that the correct price was used for the chargeback. I created a unique field in both lists combining the contract id and product id. I was able to validate the contract price but not efficiently. How do I lookup the entire table and find the proper result? I think I need a nested formula but not sure. Hope this makes sense.

 

[David Evans]

Well, I think what you're trying to do is the equivalent of a "lookup" based on multiple criteria.
There's an interesting way of applying SUMPRODUCT to achieve this - I've added a column to your Chargeback table that essentially identifies the correct value in your Contracts table - I took the liberty of making them into Excel Tables and naming them appropriately so that the formula is easier to comprehend.

It appears to return the same values as your Contract Price column.

Hope it helps.

For a good primer on how you can use SUMPRODUCT, search Chandoo's archives - it's been done several times here and is well explained. Here's another source I found equally insightful http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[colans]

Thanks David. Unfortunately working with tables is completely new for me. I tried to replicate what you did but it's not working for me. If anyone has another solution I'd greatly appreciate the help.

 

[NARAYANK991]

Hi ,

David's SUMPRODUCT formula can be used with your existing data as follows :

=SUMPRODUCT((A25 = $A$4:$A$21) * (F25 >= $E$4:$E$21) * (F25 <= $F$4:$F$21), $G$4:$G$21)

Enter this in any unused cell in row 25 , say K25 , and copy down.

This will work only if there are no duplicates.

Narayan

 

[colans]

Narayan, thank you, that formula worked!