Vlook formulae to solve

carlleese24

Board Regular
Joined
Mar 15, 2005
Messages
106
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

yesterdays

Active Member
Joined
Oct 8, 2014
Messages
337
ABCDEFG
130/04/2015name 1101/05/2015name 21
210/05/2015name 1202/05/2015name 21
320/05/2015name 1303/05/2015name 31
425/04/2015name 2104/05/2015name 21
505/05/2015name 2205/05/2015name 22
620/05/2015name 2306/05/2015name 32
701/04/2015name 3107/05/2015name 32
805/05/2015name 3208/05/2015name 11
910/05/2015name 3309/05/2015name 11
1010/05/2015name 12
1111/05/2015name 33
1212/05/2015name 12
1313/05/2015name 12
1414/05/2015name 12
1515/05/2015name 12
1616/05/2015name 12
1717/05/2015name 12
18/05/2015name 22
19/05/2015name 22
20/05/2015name 23
21/05/2015name 23
22/05/2015name 13
23/05/2015name 13
01/05/2015name 21
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top