I have an excel document where I am trying to compare a few lists.
In one tab I have a list of customers who qualify for a discount. Most customers qualify for lots of different discounts so the customer ID is in the document multiple times.
In another tab I have a list of Orders from customers. Again, customers order multiple times so the customerID again appears multiple times.
At a baseline I am trying to say in the Orders tab...if a customerID on a specific row appears in the Discounts tab then return the discount they got on that order.
There are other criteria I need to check before looking to see if the customerID is in both tabs, so this will be a multiple condition formula, but the part of the formula that keeps breaking it is checking the CustomerID and I think it is because in the Discount tab the formula is struggling with the multiple entries of the customerID.
I have tried this with pretty much every formula combination I can think of…(if(and), sumifs, sumproduct, index(match(if)) and I keep running into road blocks.
Any ideas/thoughts would be much appreciated.
The formula I am most recently trying is:
=IFERROR(INDEX($N$2:$N$16,MATCH(1,IF($S2=0,IF('Special Discounts'!$A$2:$A$24='Sql Data'!$D2,1)),0)),0)
Where N contains the discount they were given, 'Special Discounts' A contains the customerIDs that qualify for the discount and 'SQL Data'D contains the customer who placed the order.
And I can't for the life of me figure out how to add my sample data...so help with that would also be appreciated. I am in Excel 2010
x
Thanks in advance for any support!
Shante
In one tab I have a list of customers who qualify for a discount. Most customers qualify for lots of different discounts so the customer ID is in the document multiple times.
In another tab I have a list of Orders from customers. Again, customers order multiple times so the customerID again appears multiple times.
At a baseline I am trying to say in the Orders tab...if a customerID on a specific row appears in the Discounts tab then return the discount they got on that order.
There are other criteria I need to check before looking to see if the customerID is in both tabs, so this will be a multiple condition formula, but the part of the formula that keeps breaking it is checking the CustomerID and I think it is because in the Discount tab the formula is struggling with the multiple entries of the customerID.
I have tried this with pretty much every formula combination I can think of…(if(and), sumifs, sumproduct, index(match(if)) and I keep running into road blocks.
Any ideas/thoughts would be much appreciated.
The formula I am most recently trying is:
=IFERROR(INDEX($N$2:$N$16,MATCH(1,IF($S2=0,IF('Special Discounts'!$A$2:$A$24='Sql Data'!$D2,1)),0)),0)
Where N contains the discount they were given, 'Special Discounts' A contains the customerIDs that qualify for the discount and 'SQL Data'D contains the customer who placed the order.
And I can't for the life of me figure out how to add my sample data...so help with that would also be appreciated. I am in Excel 2010
x
Thanks in advance for any support!
Shante
