Vlook formulae to solve

[carlleese24]

Hi

I was wondering if anyone can solve the following problem for me.

On sheet 1 on each row it has an action date, name and info.

On sheet 2 is where I will input the dates, name and the third column is info that I will obtained from sheet 1 depending on the input date. The input date could be 01/05/2015 and the name could be name 1 so it will obtain 1 from sheet 1 as this is between the 1st trans which is 30/04/2015 and the 2nd trans which is 10/05/2015.


I would like to create some sort of vlookup or a different formulae if needed on column C on sheet 2 that will look for the nearest date and the relevant name and return the C column in sheet 1.

This is on sheet 1

COL A COL B COL C

30/04/2015 name 1 1
10/05/2015 name 1 2
20/05/2015 name 1 3
25/04/2015 name 2 1
05/05/2015 name 2 2
20/05/2015 name 2 3
01/04/2015 name 3 1
05/05/2015 name 3 2
10/05/2015 name 3 3


This is on sheet 2
COL A COL B COL C

01/05/2015 Name 1 1
02/05/2015 Name 1 1
03/05/2015 Name 1 1
04/05/2015 Name 1 1
05/05/2015 Name 1 1
06/05/2015 Name 1 1
07/05/2015 Name 1 1
08/05/2015 Name 1 1
09/05/2015 Name 1 1
10/05/2015 Name 1 2
11/05/2015 Name 1 2
12/05/2015 Name 1 2
13/05/2015 Name 1 2
14/05/2015 Name 1 2
15/05/2015 Name 1 2
16/05/2015 Name 1 2
17/05/2015 Name 1 2
18/05/2015 Name 1 2
19/05/2015 Name 1 2
20/05/2015 Name 1 3
21/05/2015 Name 1 3
22/05/2015 Name 1 3
23/05/2015 Name 1 3
01/05/2015 Name 2 1
02/05/2015 Name 2 1
03/05/2015 Name 2 1
04/05/2015 Name 2 1
05/05/2015 Name 2 2
06/05/2015 Name 2 2
07/05/2015 Name 2 2
08/05/2015 Name 2 2
09/05/2015 Name 2 2
10/05/2015 Name 2 3
11/05/2015 Name 2 3
12/05/2015 Name 2 3
13/05/2015 Name 2 3
14/05/2015 Name 2 3
15/05/2015 Name 2 3
16/05/2015 Name 2 3
17/05/2015 Name 2 3
18/05/2015 Name 2 3
19/05/2015 Name 2 3
20/05/2015 Name 2 3
21/05/2015 Name 2 3
22/05/2015 Name 2 3
23/05/2015 Name 2 3


Hope someone can help on this


Kind regards

Carl

 

[yesterdays]

	A	B	C	D	E	F	G
1	30/04/2015	name 1	1		01/05/2015	name 2	1
2	10/05/2015	name 1	2		02/05/2015	name 2	1
3	20/05/2015	name 1	3		03/05/2015	name 3	1
4	25/04/2015	name 2	1		04/05/2015	name 2	1
5	05/05/2015	name 2	2		05/05/2015	name 2	2
6	20/05/2015	name 2	3		06/05/2015	name 3	2
7	01/04/2015	name 3	1		07/05/2015	name 3	2
8	05/05/2015	name 3	2		08/05/2015	name 1	1
9	10/05/2015	name 3	3		09/05/2015	name 1	1
10					10/05/2015	name 1	2
11					11/05/2015	name 3	3
12					12/05/2015	name 1	2
13					13/05/2015	name 1	2
14					14/05/2015	name 1	2
15					15/05/2015	name 1	2
16					16/05/2015	name 1	2
17					17/05/2015	name 1	2
					18/05/2015	name 2	2
					19/05/2015	name 2	2
					20/05/2015	name 2	3
					21/05/2015	name 2	3
					22/05/2015	name 1	3
					23/05/2015	name 1	3
					01/05/2015	name 2	1
					02/05/2015
					03/05/2015

<tbody>
</tbody>

G1:
=INDEX($C$2:$C$10,MATCH(E2,IF($A$2:$A$10*($B$2:$B$10=F2)=0,"",$A$2:$A$10*($B$2:$B$10=F2)),1))
Ctrl Shift Enter

Modify it as your need.