VBA Vlookup

wangaa11

New Member
Joined
Feb 28, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
Hello, I have a list of data that is roughly over 1000 rows and 10 columns although I only really need the first three columns. Below is an example of the data, at first I was looking to just use a vlookup based on "today's" date but the data extraction is not consistent. I only want to return the first three columns based on "today's" date. Any help or advice is greatly appreciated, I am trying to avoid a bunch of manual manipulation of the data if possible.

ABCDEFGHIJ
111/26/2020 14:00Smith Johnsmithj@hotmail.com7057178888BlankCBService LocationAddress5One on One
211/26/2020 14:05Smith Johnjsmith@gmail.com4162222222BlankCBService LocationAddress5One on One
311/26/2020 14:10
Smith John
xxxx@outlook.ca5554446666BlankCBService LocationAddress5One on One
411/27/2020 9:00 AMSmith John1234@xyz.ca7057899999BlankCBService LocationAddress5One on One
511/27/2020 2:00 PMSmith Janesmithj@outlook.ca5551238888BlankCBService LocationAddress5One on One
611/27/2020 2:00 PMLast First Name1234@hotmail.com5554567777BlankCBService LocationAddress5One on One
711/28/2020 11:00doe Jane456@hotmail.com5554568888BlankCBService LocationAddress5One on One
811/28/2020 14:00doe john123@gmail.com5554568888BlankCBService LocationAddress5One on One
911/28/2020 14:05doe jj789@xxx.com5554568888BlankCBService LocationAddress5One on One
1011/28/2020 2:10 PMsmith hxxxx@hotmail.com5557891111BlankCBService LocationAddress5One on One
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Is the data in column A numeric or text?
What does the formula =ISNUMBER(A6) return? Also try that formula pointed at a few other cells in column A to see if the result is consistent.
 

wangaa11

New Member
Joined
Feb 28, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
Is the data in column A numeric or text?
What does the formula =ISNUMBER(A6) return? Also try that formula pointed at a few other cells in column A to see if the result is consistent.
I believe it is text, format states "general" and when I do ISNUMBER I get a "false".

I do only want to return columns A, B & C.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
See if you can adapt/tweak this. Note that my dates are in d/m/y order and that I do not know if your column A dates will always use two digits for day &/or month.
Post back with more details and examples if this does not work for all cases for you (after adjusting for your m/d/y order)
The date here is 1 December as shown in A12 below.

20 12 01.xlsm
ABCDEFGHIJ
130/11/2020 14:00Smith Johnsmithj@hotmail.com7057178888BlankCBService LocationAddress5One on One
230/11/2020 14:05Smith Johnjsmith@gmail.com4162222222BlankCBService LocationAddress5One on One
330/11/2020 14:10Smith Johnxxxx@outlook.ca5554446666BlankCBService LocationAddress5One on One
41/12/2020 9:00 AMSmith John1234@xyz.ca7057899999BlankCBService LocationAddress5One on One
51/12/2020 2:00 PMSmith Janesmithj@outlook.ca5551238888BlankCBService LocationAddress5One on One
61/12/2020 15:00Last First Name1234@hotmail.com5554567777BlankCBService LocationAddress5One on One
72/12/2020 11:00doe Jane456@hotmail.com5554568888BlankCBService LocationAddress5One on One
82/12/2020 14:00doe john123@gmail.com5554568888BlankCBService LocationAddress5One on One
92/12/2020 14:05doe jj789@xxx.com5554568888BlankCBService LocationAddress5One on One
102/12/2020 2:10 PMsmith hxxxx@hotmail.com5557891111BlankCBService LocationAddress5One on One
11
121/12/2020
13
141/12/2020 9:00 AMSmith John1234@xyz.ca
151/12/2020 2:00 PMSmith Janesmithj@outlook.ca
161/12/2020 15:00Last First Name1234@hotmail.com
17   
18   
Today
Cell Formulas
RangeFormula
A12A12=TODAY()
A14:C18A14=IFERROR(INDEX(A$1:A$10,AGGREGATE(15,6,(ROW(A$1:A$10)-ROW(A$1)+1)/(LEFT($A$1:$A$10,FIND(" ",$A$1:$A$10)-1)=TEXT($A$12,"d/mm/yyyy")),ROWS(A$14:A14))),"")
 

wangaa11

New Member
Joined
Feb 28, 2020
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
See if you can adapt/tweak this. Note that my dates are in d/m/y order and that I do not know if your column A dates will always use two digits for day &/or month.
Post back with more details and examples if this does not work for all cases for you (after adjusting for your m/d/y order)
The date here is 1 December as shown in A12 below.

20 12 01.xlsm
ABCDEFGHIJ
130/11/2020 14:00Smith Johnsmithj@hotmail.com7057178888BlankCBService LocationAddress5One on One
230/11/2020 14:05Smith Johnjsmith@gmail.com4162222222BlankCBService LocationAddress5One on One
330/11/2020 14:10Smith Johnxxxx@outlook.ca5554446666BlankCBService LocationAddress5One on One
41/12/2020 9:00 AMSmith John1234@xyz.ca7057899999BlankCBService LocationAddress5One on One
51/12/2020 2:00 PMSmith Janesmithj@outlook.ca5551238888BlankCBService LocationAddress5One on One
61/12/2020 15:00Last First Name1234@hotmail.com5554567777BlankCBService LocationAddress5One on One
72/12/2020 11:00doe Jane456@hotmail.com5554568888BlankCBService LocationAddress5One on One
82/12/2020 14:00doe john123@gmail.com5554568888BlankCBService LocationAddress5One on One
92/12/2020 14:05doe jj789@xxx.com5554568888BlankCBService LocationAddress5One on One
102/12/2020 2:10 PMsmith hxxxx@hotmail.com5557891111BlankCBService LocationAddress5One on One
11
121/12/2020
13
141/12/2020 9:00 AMSmith John1234@xyz.ca
151/12/2020 2:00 PMSmith Janesmithj@outlook.ca
161/12/2020 15:00Last First Name1234@hotmail.com
17   
18   
Today
Cell Formulas
RangeFormula
A12A12=TODAY()
A14:C18A14=IFERROR(INDEX(A$1:A$10,AGGREGATE(15,6,(ROW(A$1:A$10)-ROW(A$1)+1)/(LEFT($A$1:$A$10,FIND(" ",$A$1:$A$10)-1)=TEXT($A$12,"d/mm/yyyy")),ROWS(A$14:A14))),"")
This helped thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,127,522
Messages
5,625,305
Members
416,089
Latest member
Captkraken33

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