A nested lookup?

[bs0d]

Hello,

I have a workbook with 2 tabs:

1 - list of ID's, and date.
2 - archive of ID's and multiple dates (sorted by ID, then by date).


I need a forumla that will lookup (in tab 2) the ID in tab 1. Then if found, lookup the dates for that ID that is the closest match to the date in tab 1.

 

[venkat1926]

sheet 1 sample data is like this from A1 to B6

IDs date
1 2-Mar-11
2 7-Feb-11
3 6-Jan-11
4 5-Mar-11
5 25-Feb-11

in sheet2 sample data is like this

id dates
1 9-Feb-11
1 24-Mar-11
1 5-Feb-11
1 5-Apr-11
2 6-Feb-11
2 27-Jan-11
2 25-Jan-11
3 11-Jan-11
3 12-Feb-11
4 1-Mar-11
4 19-Mar-11
4 13-Feb-11
4 5-Apr-11
5 7-Mar-11
5 29-Mar-11
5 7-Apr-11
in sheet 2 in C2 type this formula

=ABS(VLOOKUP(A2,Sheet1!$A$2:$B$6,2,FALSE)-B2)

copy C2 down.

the data in sheet2 sort the data first according to column A ascending and second column C repeat column C ascending.


after sorting the data in sheet 2 will be like this

id dates DAYS BETWEEN
1 9-Feb-11 21
1 24-Mar-11 22
1 5-Feb-11 25
1 5-Apr-11 34
2 6-Feb-11 1
2 27-Jan-11 11
2 25-Jan-11 13
3 11-Jan-11 5
3 12-Feb-11 37
4 1-Mar-11 4
4 19-Mar-11 14
4 13-Feb-11 20
4 5-Apr-11 31
5 7-Mar-11 10
5 29-Mar-11 32
5 7-Apr-11 41


now go to sheet1 and C2 type this formula

=VLOOKUP($A2,Sheet2!$A$1:$C$17,COLUMN(B1),FALSE)
copy C2 to D2
highlgiht c2:D2 and copy down

you will get the folloiwng

IDs date nearest abs difference
1 2-Mar-11 9-Feb-11 21
2 7-Feb-11 6-Feb-11 1
3 6-Jan-11 11-Jan-11 5
4 5-Mar-11 1-Mar-11 4
5 25-Feb-11 7-Mar-11 10

sometimes the dates will be in nunbers format them as dates.


this is because you asked for a formula . You can have a macro for this.

 

[bs0d]

Thanks!