I have the following two tables, the first lists the Employee Increase
Dates and the Amounts, the second table has the Employee and their Pay
Date. In the second table I need to lookup the Amount equal, or prior,
to the Pay Date. For example ID737924 and Pay Date 31-Aug-02 should
show Amount 14,850. A suggested formula was
=INDEX(C4:C28,MATCH(MAX(IF((A4:A28=A30)*(B4:B28<=B 30),B4:B28)),B4:B28,0))
where C=Amount, A=ID and B=Increase. However this formula will work on a
small sample but when applied to the live data it usually returns an
error (#N/A). The data is in excess of 20,000 entries. In addition the
ID can be numeric or alpha numeric and I have spilt the ID into Numeric
and Alpha Numeric sections. Bob has been a tremendous help but I wonder
if anyone else can suggest another solution?

ID Increase Amount
737924 06-Feb-01 15,500.00
737924 01-Sep-01 14,850.00
737924 15-Apr-02 14,850.00
737924 01-Sep-02 15,225.00
738302 05-Feb-01 27,500.00
738302 01-Sep-01 28,875.00
738302 01-Sep-02 29,750.00
738575 01-May-01 16,500.00
738583 01-Sep-00 29,000.00
738583 01-Sep-01 27,400.00
738591 01-Sep-00 44,000.00
738591 01-Sep-01 48,000.00
738591 01-Sep-02 51,000.00
738609 01-Sep-00 24,229.92
738609 01-Sep-01 23,653.34
738609 15-May-02 23,653.34
738609 01-Sep-02 24,200.00
773101 12-Mar-01 40,000.00
773101 01-Sep-01 38,600.00
773101 01-Sep-02 39,370.00
799437 05-Mar-01 11,000.00
799437 01-Jun-01 14,000.00
799437 01-Oct-01 14,500.00
799437 01-Sep-02 14,900.00
799437 02-Sep-02 15,000.00
ID Pay Date
737924 30-Aug-02
737924 30-Aug-02
737924 27-Sep-02
737924 27-Sep-02
737924 25-Oct-02
737924 25-Oct-02
737924 29-Nov-02
737924 29-Nov-02
737924 20-Dec-02
737924 20-Dec-02
738302 25-Oct-02
738302 25-Oct-02
738302 29-Nov-02
738302 29-Nov-02
738302 20-Dec-02
738302 20-Dec-02
773101 30-Aug-02
773101 30-Aug-02
773101 27-Sep-02
773101 27-Sep-02
773101 25-Oct-02
773101 25-Oct-02
773101 29-Nov-02
773101 29-Nov-02
773101 20-Dec-02
773101 20-Dec-02
799437 26-Apr-02
799437 26-Apr-02
799437 31-May-02
799437 31-May-02
799437 28-Jun-02
799437 28-Jun-02
799437 26-Jul-02
799437 26-Jul-02
799437 30-Aug-02
799437 30-Aug-02


--
CharlesF
------------------------------------------------------------------------
CharlesF's Profile: http://www.excelforum.com/member.php...fo&userid=8743
View this thread: http://www.excelforum.com/showthread...hreadid=502186