Difficult Two Column Lookup

[CharlesF]

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<=B30),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

 

[Pete]

If you look at the formula in more detail:

=INDEX(C4:C28,MATCH(MAX(IF((A4:A28=A30)*(B4:B28<=B30),B4:B28)),B4:B28,0))

you can see a number of ranges here, i.e. A4:A28, B4:B28, C4:C28 which
relate to your top table in the example. You are comparing A30 and B30
with values in these ranges, and presumably copying the formula down so
that A30 becomes A31, A32 etc.

Are the ranges still valid in your live file, or is the example just an
extract? I would suggest making the ranges absolute, eg A$4:A$28
(ensuring that this does cover your table completely) or use named
ranges to avoid confusion. Ensure that that the A30 and B30 terms do
actually relate to the row that the formula starts on.

Also, I think this is an array formula - did you do CTRL-SHIFT-ENTER
when you put it into your live data?

Hope this helps.

Pete

 

[CharlesF]

Hi Pete

Yes this is an example.

In the live data, the ranges are absolute and I presses CTRL+SHT+ENTER
when committing.

Any other suggestions?