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