multiple lookup including date range

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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)