software

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:B28lt;=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?

IDIncreaseAmount
73792406-Feb-0115,500.00
73792401-Sep-0114,850.00
73792415-Apr-0214,850.00
73792401-Sep-0215,225.00
73830205-Feb-0127,500.00
73830201-Sep-0128,875.00
73830201-Sep-0229,750.00
73857501-May-0116,500.00
73858301-Sep-0029,000.00
73858301-Sep-0127,400.00
73859101-Sep-0044,000.00
73859101-Sep-0148,000.00
73859101-Sep-0251,000.00
73860901-Sep-0024,229.92
73860901-Sep-0123,653.34
73860915-May-0223,653.34
73860901-Sep-0224,200.00
77310112-Mar-0140,000.00
77310101-Sep-0138,600.00
77310101-Sep-0239,370.00
79943705-Mar-0111,000.00
79943701-Jun-0114,000.00
79943701-Oct-0114,500.00
79943701-Sep-0214,900.00
79943702-Sep-0215,000.00
IDPay Date
73792430-Aug-02
73792430-Aug-02
73792427-Sep-02
73792427-Sep-02
73792425-Oct-02
73792425-Oct-02
73792429-Nov-02
73792429-Nov-02
73792420-Dec-02
73792420-Dec-02
73830225-Oct-02
73830225-Oct-02
73830229-Nov-02
73830229-Nov-02
73830220-Dec-02
73830220-Dec-02
77310130-Aug-02
77310130-Aug-02
77310127-Sep-02
77310127-Sep-02
77310125-Oct-02
77310125-Oct-02
77310129-Nov-02
77310129-Nov-02
77310120-Dec-02
77310120-Dec-02
79943726-Apr-02
79943726-Apr-02
79943731-May-02
79943731-May-02
79943728-Jun-02
79943728-Jun-02
79943726-Jul-02
79943726-Jul-02
79943730-Aug-02
79943730-Aug-02--
CharlesF
------------------------------------------------------------------------
CharlesF's Profile: www.excelforum.com/member.php...foamp;userid=8743
View this thread: www.excelforum.com/showthread...hreadid=502186If you look at the formula in more detail:

=INDEX(C4:C28,MATCH(MAX(IF((A4:A28=A30)*(B4:B28lt;=B 30),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
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?--
CharlesF
------------------------------------------------------------------------
CharlesF's Profile: www.excelforum.com/member.php...foamp;userid=8743
View this thread: www.excelforum.com/showthread...hreadid=502186