Index & Match

[GolfGal]

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

 

[Bernie Deitrick]

Golf Gal,

You can use a formula like

=SUMPRODUCT((D210="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]

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???