Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Index & Match
From reading here, it appears that Index & Match are the function I need, but
I can't seem to get it to do what I need. Let's say I receive a price list from a wholesaler each morning (cannot be sorted). The same products may not necessarily be in the same rows each day, depending on availability. Simple example: DAYS Disc PRICE PRODUCT 30 5% 19.99 Pants 60 10% 24.99 Shirt 90 20% 64.99 Dress 30 5% 32.99 Pants 60 10% 18.99 Shirt 90 20% 84.99 Dress 30 5% 29.99 Pants 60 10% 9.99 Shirt 90 20% 29.99 Dress Here is what I'd like the function to do: Find single product, e.g. pants in column d Find only pants that I can buy at that price for 60 days (colA) This would refer to another cell on my worksheet named "days" From that list of pants that I can buy at that price for a certain number of days, Find only pants that offer a certain discount , e.g. 20% This would also refer to another cell on my worksheet named "discount" Finally, the result of the formula needs to be the price of these pants that I can buy for 60 days at a 20% discount. Is it possible for an excel function/formula to do this? Thanks |
#2
|
|||
|
|||
Golf Gal,
You can use a formula like =SUMPRODUCT((D2:D10="Pants")*(A2:A10=days)*(B2:B10 =discount)*(C2:C10)) but that would require that there be only one netry of pants for x days at y discount. In your example table, you have 3 entries for pants at 30 days at a 5% discount, which would cause this to return the sum of all three of those prices. HTH, Bernie MS Excel MVP "GolfGal" wrote in message ... From reading here, it appears that Index & Match are the function I need, but I can't seem to get it to do what I need. Let's say I receive a price list from a wholesaler each morning (cannot be sorted). The same products may not necessarily be in the same rows each day, depending on availability. Simple example: DAYS Disc PRICE PRODUCT 30 5% 19.99 Pants 60 10% 24.99 Shirt 90 20% 64.99 Dress 30 5% 32.99 Pants 60 10% 18.99 Shirt 90 20% 84.99 Dress 30 5% 29.99 Pants 60 10% 9.99 Shirt 90 20% 29.99 Dress Here is what I'd like the function to do: Find single product, e.g. pants in column d Find only pants that I can buy at that price for 60 days (colA) This would refer to another cell on my worksheet named "days" From that list of pants that I can buy at that price for a certain number of days, Find only pants that offer a certain discount , e.g. 20% This would also refer to another cell on my worksheet named "discount" Finally, the result of the formula needs to be the price of these pants that I can buy for 60 days at a 20% discount. Is it possible for an excel function/formula to do this? Thanks |
#3
|
|||
|
|||
Thanks for the response.
I'm not trying to add anything up. I'm just trying to get it to lookup and enter the value. I would just need it to lookup. It would be similar to applying the auto filter and selecting only 'pants'. Then applying the 'days' filter and showing only pants listed in the 60 day column. Again select only the pants w/ the right discount, would narrow it down to only a few, all at different prices, from which I could then select only the price I want. However, doing this auto filter process separately for thousands of items would take all day. Does that make my question any clearer? Or did I throw in more mud??? "Bernie Deitrick" wrote: Golf Gal, You can use a formula like =SUMPRODUCT((D2:D10="Pants")*(A2:A10=days)*(B2:B10 =discount)*(C2:C10)) but that would require that there be only one netry of pants for x days at y discount. In your example table, you have 3 entries for pants at 30 days at a 5% discount, which would cause this to return the sum of all three of those prices. HTH, Bernie MS Excel MVP "GolfGal" wrote in message ... From reading here, it appears that Index & Match are the function I need, but I can't seem to get it to do what I need. Let's say I receive a price list from a wholesaler each morning (cannot be sorted). The same products may not necessarily be in the same rows each day, depending on availability. Simple example: DAYS Disc PRICE PRODUCT 30 5% 19.99 Pants 60 10% 24.99 Shirt 90 20% 64.99 Dress 30 5% 32.99 Pants 60 10% 18.99 Shirt 90 20% 84.99 Dress 30 5% 29.99 Pants 60 10% 9.99 Shirt 90 20% 29.99 Dress Here is what I'd like the function to do: Find single product, e.g. pants in column d Find only pants that I can buy at that price for 60 days (colA) This would refer to another cell on my worksheet named "days" From that list of pants that I can buy at that price for a certain number of days, Find only pants that offer a certain discount , e.g. 20% This would also refer to another cell on my worksheet named "discount" Finally, the result of the formula needs to be the price of these pants that I can buy for 60 days at a 20% discount. Is it possible for an excel function/formula to do this? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |