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]

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]30/04/2015[/TD]
[TD]name 1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]01/05/2015[/TD]
[TD]name 2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10/05/2015[/TD]
[TD]name 1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]02/05/2015[/TD]
[TD]name 2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]20/05/2015[/TD]
[TD]name 1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]03/05/2015[/TD]
[TD]name 3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]25/04/2015[/TD]
[TD]name 2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]04/05/2015[/TD]
[TD]name 2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]05/05/2015[/TD]
[TD]name 2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]05/05/2015[/TD]
[TD]name 2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]20/05/2015[/TD]
[TD]name 2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]06/05/2015[/TD]
[TD]name 3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]01/04/2015[/TD]
[TD]name 3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]07/05/2015[/TD]
[TD]name 3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]05/05/2015[/TD]
[TD]name 3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]08/05/2015[/TD]
[TD]name 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]10/05/2015[/TD]
[TD]name 3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]09/05/2015[/TD]
[TD]name 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/05/2015[/TD]
[TD]name 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11/05/2015[/TD]
[TD]name 3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12/05/2015[/TD]
[TD]name 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13/05/2015[/TD]
[TD]name 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/05/2015[/TD]
[TD]name 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15/05/2015[/TD]
[TD]name 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16/05/2015[/TD]
[TD]name 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17/05/2015[/TD]
[TD]name 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/05/2015[/TD]
[TD]name 2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19/05/2015[/TD]
[TD]name 2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20/05/2015[/TD]
[TD]name 2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21/05/2015[/TD]
[TD]name 2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22/05/2015[/TD]
[TD]name 1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23/05/2015[/TD]
[TD]name 1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/05/2015[/TD]
[TD]name 2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.