Cust & Product lookup pricing between date ranges

**Bobo Fisherman** · 07-22-2009, 03:59 PM

Hi excel experts. Hope you can point me in the right direction. I've been struggling for a few days without success. I think I need a lookup with match &/or index, but can't figure it out.
I have invoice extract by cust & product with billing date as key. I need to lookup correct pricing based on a separate pricing extract with Cust / Prod & 2 columns having date start & date end ... this table is 30M+ rows big.
I concatenated the Cust & Prod thinking this would help with vlookup, but I know little about match/index functions.
Attached example file for your reference.

**Special-K** · 07-22-2009, 04:53 PM

Try this, it sort of works (I think)

1) In column O enter
=SUMPRODUCT((B2>='pricing extract'!E:E)*(B2<='pricing extract'!F:F)*(H2='pricing extract'!A:A)*(I2='pricing extract'!B:B),'pricing extract'!E:E)

and format column E as dates

2) In column P enter
=SUMPRODUCT((B2>='pricing extract'!E:E)*(B2<='pricing extract'!F:F)*(H2='pricing extract'!A:A)*(I2='pricing extract'!B:B),'pricing extract'!G:G)

3) In column Q enter
=SUMPRODUCT((B2>='pricing extract'!E:E)*(B2<='pricing extract'!F:F)*(H2='pricing extract'!A:A)*(I2='pricing extract'!B:B),'pricing extract'!I:I)

This doesnt work in column R as the data in 'pricing extract'!J:J is text, am sure someone will find the solution to this column though

**Bobo Fisherman** · 07-22-2009, 05:01 PM

Thanks for the response Special K, but all the entries I need in cols O:R are text entries. I thought the sumproduct provides a summation ... not what I want; I want the lookup feature for the particular Cust/Prod/Date range

Cust & Product lookup pricing between date ranges

LinkBack

Thread Tools

Rate This Thread

Display

Cust & Product lookup pricing between date ranges

Re: Cust & Product lookup pricing between date ranges

Re: Cust & Product lookup pricing between date ranges

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions