Extract vlook up dates

[doriannjeshi]

Hello,

I need to look up dates for a table in this format!

pyth new.xlsb
	I	J	K
1	1940	date	Sign
2	Jan 1 – Jan 31 Scorpio	1/15/1941	?
3	Feb 1 – Mar 25 Sagittarius
4	Mar 26 – Jun 5 Capricorn
5	June 6 – Sep 28 Sagittarius
6	Sep 29 – Nov 29 Capricorn
7	Nov 30 – Dec 31 Aquarius
8	1941
9	Jan 1 – Jan 20 Aquarius
10	Jan 21 – Mar 7 Pisces
11	Mar 8 – Apr 17 Aries
12	Apr 18 – May 24 Taurus
13	May 25 – Jun 28 Gemini
14	Jun 29 – Jul 31 Cancer
15	Aug 1 – Sep 1 Leo
16	Sep 2 – Oct 5 Virgo
17	Oct 6 – Nov 10 Libra
18	Nov 11 Scorpio
19	Dec 20 Sagittarius
20	1942
21	Jan 1 – Feb 2 Sagittarius
22	Feb 3 – Mar 26 Capricorn
23	Mar 27 – Dec 10 Aquarius
24	Dec 11 – Dec 31 Pisces
25	1943
26	Jan 1 – Jan 27 Pisces
27	Jan 28 – Mar 7 Aries
28	Mar 8 – Apr 11 Taurus
29	Apr 12 – May 14 Gemini
30	May 15 – Jun 16Cancer
31	Jun 17 – Jul 19 Leo
32	Jul 20 – Aug 23Virgo
33	Aug 24 – Sep 30 Libra
34	Oct 1 – Nov 11Scorpio
35	Nov 12 – Dec 27 Sagittarius
36	Dec 28 – Dec 31 Capricorn
37	1944
38	Jan 1 – Feb 15 Capricorn
39	Feb 16 – Apr 8 Aquarius
40	Apr 9 – Jun 4 Pisces
41	Jun 5 – Aug 8 Aries
42	Aug 9 – Nov 6 Taurus
43	Nov 7 – Dec 31 Aries
44	1945
eros&psyche

 

[Automatrix]

Here's a solution, but you need to arrange the data in column I into two different columns. The TRUE part of the formula is key, as it will make the VLOOKUP look for the next smallest value if it can't find the exact date you are looking for. This also means that "Date start" in the left table MUST be sorted ascendingly.

Book1
	A	B	C	D	E
1	Date start	Category		Date	Category
2	01/01/1940	Scorpio		15/01/1940	Scorpio
3	01/02/1940	Sagittarius		15/02/1940	Sagittarius
4	26/03/1940	Capricorn		30/10/1940	Capricorn
5	06/06/1940	Sagittarius
6	29/09/1940	Capricorn
7	30/11/1940	Aquarius
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=VLOOKUP(D2,$A$2:$B$7,2,TRUE)

 

[Peter_SSs]

Is your data really that variable in format?
Examples
1. All leading month name are abbreviated to 3 letters - apart from row 5
2. Most of the Sign name are preceded by a space - but not rows 30, 32 7 34

Can you get the data in a uniform format?

 

[doriannjeshi]

Here's a solution, but you need to arrange the data in column I into two different columns. The TRUE part of the formula is key, as it will make the VLOOKUP look for the next smallest value if it can't find the exact date you are looking for. This also means that "Date start" in the left table MUST be sorted ascendingly.

Book1
	A	B	C	D	E
1	Date start	Category		Date	Category
2	01/01/1940	Scorpio		15/01/1940	Scorpio
3	01/02/1940	Sagittarius		15/02/1940	Sagittarius
4	26/03/1940	Capricorn		30/10/1940	Capricorn
5	06/06/1940	Sagittarius
6	29/09/1940	Capricorn
7	30/11/1940	Aquarius
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=VLOOKUP(D2,$A$2:$B$7,2,TRUE)

Thank you Automatrix for the response and the solution!

 

[doriannjeshi]

Is your data really that variable in format?
Examples
1. All leading month name are abbreviated to 3 letters - apart from row 5
2. Most of the Sign name are preceded by a space - but not rows 30, 32 7 34

Can you get the data in a uniform format?

1. I can fix all months having only 3 letters
2. I will fix this too

Yes I can uniform the format!

 

[Peter_SSs]

Yes I can uniform the format!

In that case a somewhat similar solution would be to insert a new column immediately to the left of the date/sign column and enter the formula shown. That new column could then be hidden if you want.
Formula in col K to extract the result.

22 12 03.xlsm
	H	I	J	K
1		1940	Date	Sign
2	1/01/1940	Jan 1 – Jan 31 Scorpio	15/01/1941	Aquarius
3	1/02/1940	Feb 1 – Mar 25 Sagittarius	21/07/1943	Virgo
4	26/03/1940	Mar 26 – Jun 5 Capricorn	21/07/1942	Aquarius
5	6/06/1940	Jun 6 – Sep 28 Sagittarius	31/12/1944	Aries
6	29/09/1940	Sep 29 – Nov 29 Capricorn
7	30/11/1940	Nov 30 – Dec 31 Aquarius
8	#VALUE!	1941
9	1/01/1941	Jan 1 – Jan 20 Aquarius
10	21/01/1941	Jan 21 – Mar 7 Pisces
11	8/03/1941	Mar 8 – Apr 17 Aries
12	18/04/1941	Apr 18 – May 24 Taurus
13	25/05/1941	May 25 – Jun 28 Gemini
14	29/06/1941	Jun 29 – Jul 31 Cancer
15	1/08/1941	Aug 1 – Sep 1 Leo
16	2/09/1941	Sep 2 – Oct 5 Virgo
17	6/10/1941	Oct 6 – Nov 10 Libra
18	11/11/1941	Nov 11 Scorpio
19	20/12/1941	Dec 20 Sagittarius
20	#VALUE!	1942
21	1/01/1942	Jan 1 – Feb 2 Sagittarius
22	3/02/1942	Feb 3 – Mar 26 Capricorn
23	27/03/1942	Mar 27 – Dec 10 Aquarius
24	11/12/1942	Dec 11 – Dec 31 Pisces
25	#VALUE!	1943
26	1/01/1943	Jan 1 – Jan 27 Pisces
27	28/01/1943	Jan 28 – Mar 7 Aries
28	8/03/1943	Mar 8 – Apr 11 Taurus
29	12/04/1943	Apr 12 – May 14 Gemini
30	15/05/1943	May 15 – Jun 16 Cancer
31	17/06/1943	Jun 17 – Jul 19 Leo
32	20/07/1943	Jul 20 – Aug 23 Virgo
33	24/08/1943	Aug 24 – Sep 30 Libra
34	1/10/1943	Oct 1 – Nov 11 Scorpio
35	12/11/1943	Nov 12 – Dec 27 Sagittarius
36	28/12/1943	Dec 28 – Dec 31 Capricorn
37	#VALUE!	1944
38	1/01/1944	Jan 1 – Feb 15 Capricorn
39	16/02/1944	Feb 16 – Apr 8 Aquarius
40	9/04/1944	Apr 9 – Jun 4 Pisces
41	5/06/1944	Jun 5 – Aug 8 Aries
42	9/08/1944	Aug 9 – Nov 6 Taurus
43	7/11/1944	Nov 7 – Dec 31 Aries
44	#VALUE!	1945
Lookup

Cell Formulas
Range	Formula
K2:K5	K2	=TRIM(RIGHT(SUBSTITUTE(VLOOKUP(J2,H$2:I$1000,2)," ",REPT(" ",20)),20))
H2:H44	H2	=DATEVALUE(MID(I2,5,2)&LEFT(I2,3)&LOOKUP(9999,I$1:I1))

 

[doriannjeshi]

1. I can fix all months having only 3 letters
2. I will fix this too

Yes I can uniform the format!

1940	date	Sign
Jan 1 – Jan 31 Scorpio	1/15/1941	?
Feb 1 – Mar 25 Sagittarius
Mar 26 – Jun 5 Capricorn
Jun 6 – Sep 28 Sagittarius
Sep 29 – Nov 29 Capricorn
Nov 30 – Dec 31 Aquarius
1941
Jan 1 – Jan 20 Aquarius
Jan 21 – Mar 7 Pisces
Mar 8 – Apr 17 Aries
Apr 18 – May 24 Taurus
May 25 – Jun 28 Gemini
Jun 29 – Jul 31 Cancer
Aug 1 – Sep 1 Leo
Sep 2 – Oct 5 Virgo
Oct 6 – Nov 10 Libra
Nov 11 Scorpio
Dec 20 Sagittarius
1942
Jan 1 – Feb 2 Sagittarius
Feb 3 – Mar 26 Capricorn
Mar 27 – Dec 10 Aquarius
Dec 11 – Dec 31 Pisces
1943
Jan 1 – Jan 27 Pisces
Jan 28 – Mar 7 Aries
Mar 8 – Apr 11 Taurus
Apr 12 – May 14 Gemini
May 15 – Jun 16 Cancer
Jun 17 – Jul 19 Leo
Jul 20 – Aug 23 Virgo
Aug 24 – Sep 30 Libra
Oct 1 – Nov 11 Scorpio
Nov 12 – Dec 27 Sagittarius
Dec 28 – Dec 31 Capricorn
1944
Jan 1 – Feb 15 Capricorn
Feb 16 – Apr 8 Aquarius
Apr 9 – Jun 4 Pisces
Jun 5 – Aug 8 Aries
Aug 9 – Nov 6 Taurus
Nov 7 – Dec 31 Aries
1945

 

[doriannjeshi]

Thank you for the Solution Peter it works great !

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[doriannjeshi]

one small issue I am having with a couple of dates that I tested do not work. Even when I type the date the datevalue returns error unless I enter it as text '

	1990
32874	Jan 1 – Jan 13 Capricorn
32887	Jan 14 Aquarius
#VALUE!	March 6 Pisces
#VALUE!	April 24 Aries

	1976
#VALUE!	Jan 1 Capricorn
27761	Jan 2 – Feb 21 Aquarius
27812	Feb 22 Pisces

	1945
#VALUE!	Jan 1 – Aries
16439	Jan 2 – Feb 14 Taurus