Difficult Two Column Lookup


G

Guest

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. Bob
Phillips has been helping me with. His 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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Difficult Two Column Lookup 2
two column lookup 3
Two-Column Lookup 2
two-column lookup 7
Lookup Two Columns 5
Difficult Formula lookup 4
Lookup two different columns 10
Lookup Two Columns - Again 5

Top