multiple lookup including date range
 

I have a need to be able to lookup on three different criteria one of which
is a date which would fall between a start and end date. Example below.

My main data is product price by customer and date range.

Customer Material Price Start Date End Date
100008 10220 7.2 21/05/2006 08/07/2006

What I'm trying to do is get the price out based upon the date of an
invoice. So I have a second workbook that shows the following:

Customer Material Date of Invoice Price
100008 10220 06/06/2006 Should read 7.2
100008 10220 10/07/2006 Should fail.

providing the customer and materail match and the Invoice date is within the
range of start and end dates I need to be able to pull the price in to the
price field on the relevant workbook.

TIA

Jon

multiple lookup including date range
 

Try this entered as an array formula with Ctrl+Shift+Enter (CSE)

=IF(ISNA(INDEX($C$2:$C$4,MATCH(1,($A$2:$A$4=A7)*($ B$2:$B$4=B7)*(C7=$D$2:$D$4)*(C7<=$E$2:$E$4),0),1) ),"",INDEX($C$2:$C$4,MATCH(1,($A$2:$A$4=A7)*($B$2: $B$4=B7)*(C7=$D$2:$D$4)*(C7<=$E$2:$E$4),0),1))

My "output" (your second workbook) was in row 7 so change A7,B7,C7 to suit.

This checks Customer #, Material and Invoice Date against your price table.
If no match is found, invoice amount is left blanK.

HTH

"JonW" wrote:

I have a need to be able to lookup on three different criteria one of which
is a date which would fall between a start and end date. Example below.

My main data is product price by customer and date range.

Customer Material Price Start Date End Date
100008 10220 7.2 21/05/2006 08/07/2006

What I'm trying to do is get the price out based upon the date of an
invoice. So I have a second workbook that shows the following:

Customer Material Date of Invoice Price
100008 10220 06/06/2006 Should read 7.2
100008 10220 10/07/2006 Should fail.

providing the customer and materail match and the Invoice date is within the
range of start and end dates I need to be able to pull the price in to the
price field on the relevant workbook.

TIA

Jon

multiple lookup including date range
 

Toppers

Thankyou once I worked out what the formula was doing I was able to put it
into place in my workbooks.

Once again thankyou.

Jon

"Toppers" wrote:

Try this entered as an array formula with Ctrl+Shift+Enter (CSE)

=IF(ISNA(INDEX($C$2:$C$4,MATCH(1,($A$2:$A$4=A7)*($ B$2:$B$4=B7)*(C7=$D$2:$D$4)*(C7<=$E$2:$E$4),0),1) ),"",INDEX($C$2:$C$4,MATCH(1,($A$2:$A$4=A7)*($B$2: $B$4=B7)*(C7=$D$2:$D$4)*(C7<=$E$2:$E$4),0),1))

My "output" (your second workbook) was in row 7 so change A7,B7,C7 to suit.

This checks Customer #, Material and Invoice Date against your price table.
If no match is found, invoice amount is left blanK.

HTH

"JonW" wrote:

I have a need to be able to lookup on three different criteria one of which
is a date which would fall between a start and end date. Example below.

My main data is product price by customer and date range.

Customer Material Price Start Date End Date
100008 10220 7.2 21/05/2006 08/07/2006

What I'm trying to do is get the price out based upon the date of an
invoice. So I have a second workbook that shows the following:

Customer Material Date of Invoice Price
100008 10220 06/06/2006 Should read 7.2
100008 10220 10/07/2006 Should fail.

providing the customer and materail match and the Invoice date is within the
range of start and end dates I need to be able to pull the price in to the
price field on the relevant workbook.

TIA

Jon

multiple lookup including date range
 

Let's say:

in Sheet1
Customer in column A
Material in column B
Price in column C
Start date in column D
End date in Column E
Header in row1

In sheet2
Customer in column A
Material in column B
Date invoice in column C
Price in column D
Header in row 1

In D2:
=SUMPRODUCT(--(Sheet1!$A$2:$A$300=Sheet2!A2),--(Sheet1!$B$2:$B$300=Sheet2!B2),--(Sheet1!$D$2:$D$300<=Sheet2!C2),--(Sheet1!$E$2:$E$300=Sheet2!C2),Sheet1!$C$2:$C$300 )


"JonW" wrote:

I have a need to be able to lookup on three different criteria one of which
is a date which would fall between a start and end date. Example below.

My main data is product price by customer and date range.

Customer Material Price Start Date End Date
100008 10220 7.2 21/05/2006 08/07/2006

What I'm trying to do is get the price out based upon the date of an
invoice. So I have a second workbook that shows the following:

Customer Material Date of Invoice Price
100008 10220 06/06/2006 Should read 7.2
100008 10220 10/07/2006 Should fail.

providing the customer and materail match and the Invoice date is within the
range of start and end dates I need to be able to pull the price in to the
price field on the relevant workbook.

TIA

Jon