Nearest Less than date after vlookup

[beamo]

Hi All,

Heaven knows if I'm explaining my problem correctly but here goes.

I have some customers who can make two different types of transactions which are inter-related. Each transaction can be made many times.

So worksheet 1 lists transaction one with a customer number, effective date and payment amount.

worksheet 2 lists transaction two with a customer number, effective date and transaction data.

I want to append the relevant information from worksheet 2 to worksheet 1. The rules arethe customer number must match and the effective date I want to grab from worksheet 2 is the entry that is nearest to, but less than the effective date I am looking at in worksheet 1. Once I get that date in, it should be easy to vlookup the other details with an IF AND function.

Here's hoping I made some sense ?

 

[Jonmo1]

Welcome to the board

If the data in worksheet 2 can be sorted ASCENDING by the effective date
Then you can use vlookup on the effective date column using TRUE as the 4th argument in the vlookup.

Hope that helps.

 

[beamo]

The problem I have is there could be multiple entries in the transaction 2 worksheet that are for dates both before and after the relevant date in worksheet 1 that I am trying to get closest to.

I don't think the Vlookup will pick the nearest date relevant for the matching customer number. It will pick the first or the last depending on order, but I need it to look at each date and pick the closest.

 

[Jonmo1]

It works for me...

Provided the Data in Sheet 1 is sorted ASCENDING by the dates..
When using TRUE as 4th argument in Vlookup, it will find the closest value that is less than or equal to the lookup value.

Excel Workbook
	A	B
1	Date to find	Result
2	6/11/2011	c
3	6/24/2011	e
4	6/7/2011	a
5	7/1/2011	e
6	7/9/2011	h
Sheet2

Excel Workbook
	A	B
1	Date	Value
2	6/1/2011	a
3	6/10/2011	b
4	6/11/2011	c
5	6/19/2011	d
6	6/24/2011	e
7	7/4/2011	f
8	7/8/2011	g
9	7/9/2011	h
10	7/14/2011	i
Sheet1

 

[beamo]

Ok, looks good, but how do I incorporate the match on customer number as well.

Both sheets will hold multiple customers and I only want the vlookup to refer to the dates that are relevant for the customer number being checked ?