If & And Condition based on lookup value change at every 30 row

[mba_110]

Hi,

Attached table shows list of total degrees, i want once formula reached 30 degrees at any row, from next row it should change lookup value.

I have already mentioned the vlookup position with INDEX + MATCH function the problem is if condition for changing lookup value at every 30 row interval.

My current formula return the FALSE no desired result.

Mirza Bahadur Ali.xlsx
	V	W	X	Y	Z	AA
6	MARCH (مارچ)
7	15:47:00	Sun Set	06:22:00	Sun Raise	21-Mar	Gerg.Date
8
9	Total Degrees	Degree	End	Start	Date	Starting Sign
10	1	1	06:24:46	06:22:00	21-Mar	Aries
11	2	1	06:27:33	06:24:47	21-Mar	FALSE
12	3	1	06:30:20	06:27:34	21-Mar
13	4	1	06:33:07	06:30:21	21-Mar
14	5	1	06:35:54	06:33:08	21-Mar
15	6	1	06:38:41	06:35:55	21-Mar
16	7	1	06:41:28	06:38:42	21-Mar
17	8	1	06:44:15	06:41:29	21-Mar
18	9	1	06:47:02	06:44:16	21-Mar
19	10	1	06:49:49	06:47:03	21-Mar
20	11	1	06:52:36	06:49:50	21-Mar
21	12	1	06:55:23	06:52:37	21-Mar
22	13	1	06:58:10	06:55:24	21-Mar
23	14	1	07:00:57	06:58:11	21-Mar
24	15	1	07:03:44	07:00:58	21-Mar
25	16	1	07:06:31	07:03:45	21-Mar
26	17	1	07:09:18	07:06:32	21-Mar
27	18	1	07:12:05	07:09:19	21-Mar
28	19	1	07:14:52	07:12:06	21-Mar
29	20	1	07:17:39	07:14:53	21-Mar
30	21	1	07:20:26	07:17:40	21-Mar
31	22	1	07:23:13	07:20:27	21-Mar
32	23	1	07:26:00	07:23:14	21-Mar
33	24	1	07:28:47	07:26:01	21-Mar
34	25	1	07:31:34	07:28:48	21-Mar
35	26	1	07:34:21	07:31:35	21-Mar
36	27	1	07:37:08	07:34:22	21-Mar
37	28	1	07:39:55	07:37:09	21-Mar
38	29	1	07:42:42	07:39:56	21-Mar
39	30	1	07:45:29	07:42:43	21-Mar
40	31	1	07:48:16	07:45:30	21-Mar
41	32	1	07:51:03	07:48:17	21-Mar
42	33	1	07:53:50	07:51:04	21-Mar
43	34	1	07:56:37	07:53:51	21-Mar
44	35	1	07:59:24	07:56:38	21-Mar
45	36	1	08:02:11	07:59:25	21-Mar
46	37	1	08:04:58	08:02:12	21-Mar
47	38	1	08:07:45	08:04:59	21-Mar
48	39	1	08:10:32	08:07:46	21-Mar
49	40	1	08:13:19	08:10:33	21-Mar
50	41	1	08:16:06	08:13:20	21-Mar
51	42	1	08:18:53	08:16:07	21-Mar
52	43	1	08:21:40	08:18:54	21-Mar
53	44	1	08:24:27	08:21:41	21-Mar
54	45	1	08:27:14	08:24:28	21-Mar
55	46	1	08:30:01	08:27:15	21-Mar
56	47	1	08:32:48	08:30:02	21-Mar
57	48	1	08:35:35	08:32:49	21-Mar
58	49	1	08:38:22	08:35:36	21-Mar
59	50	1	08:41:09	08:38:23	21-Mar
60	51	1	08:43:56	08:41:10	21-Mar
61	52	1	08:46:43	08:43:57	21-Mar
62	53	1	08:49:30	08:46:44	21-Mar
63	54	1	08:52:17	08:49:31	21-Mar
64	55	1	08:55:04	08:52:18	21-Mar
65	56	1	08:57:51	08:55:05	21-Mar
66	57	1	09:00:38	08:57:52	21-Mar
67	58	1	09:03:25	09:00:39	21-Mar
68	59	1	09:06:12	09:03:26	21-Mar
69	60	1	09:08:59	09:06:13	21-Mar
70	61	1	09:11:46	09:09:00	21-Mar
71	62	1	09:14:33	09:11:47	21-Mar
72	63	1	09:17:20	09:14:34	21-Mar
73	64	1	09:20:07	09:17:21	21-Mar
74	65	1	09:22:54	09:20:08	21-Mar
75	66	1	09:25:41	09:22:55	21-Mar
76	67	1	09:28:28	09:25:42	21-Mar
77	68	1	09:31:15	09:28:29	21-Mar
78	69	1	09:34:02	09:31:16	21-Mar
79	70	1	09:36:49	09:34:03	21-Mar
Sitaron Ki Degree

Cell Formulas
Range	Formula
X10	X10	=Y10+TIME(,$F$219,$E$219)
Y10	Y10	=X7
Z10	Z10	=K219
X11:X79	X11	=IF(X10<$V$7,Y11+TIME(,$F$219,$E$219),"")
Y11:Y79	Y11	=X10+TIME(,,1)
AA10	AA10	=D219
AA11	AA11	=IF(AND(V10<30,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=30<60,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=60<90,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=90<120,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=120<150,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=150<180,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=180<210,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))
V10	V10	=W10
V11:V79	V11	=V10+W11

I hope you understand the concept, i did not mention the lookup table because its rage is already given and it will be excessive size to paste here in XL2BB.

The formula is in cell AA11

Thanks & regards.

 

[Bosquedeguate]

Hi,

Attached table shows list of total degrees, i want once formula reached 30 degrees at any row, from next row it should change lookup value.

I have already mentioned the vlookup position with INDEX + MATCH function the problem is if condition for changing lookup value at every 30 row interval.

My current formula return the FALSE no desired result.

Mirza Bahadur Ali.xlsx
	V	W	X	Y	Z	AA
6	MARCH (مارچ)
7	15:47:00	Sun Set	06:22:00	Sun Raise	21-Mar	Gerg.Date
8
9	Total Degrees	Degree	End	Start	Date	Starting Sign
10	1	1	06:24:46	06:22:00	21-Mar	Aries
11	2	1	06:27:33	06:24:47	21-Mar	FALSE
12	3	1	06:30:20	06:27:34	21-Mar
13	4	1	06:33:07	06:30:21	21-Mar
14	5	1	06:35:54	06:33:08	21-Mar
15	6	1	06:38:41	06:35:55	21-Mar
16	7	1	06:41:28	06:38:42	21-Mar
17	8	1	06:44:15	06:41:29	21-Mar
18	9	1	06:47:02	06:44:16	21-Mar
19	10	1	06:49:49	06:47:03	21-Mar
20	11	1	06:52:36	06:49:50	21-Mar
21	12	1	06:55:23	06:52:37	21-Mar
22	13	1	06:58:10	06:55:24	21-Mar
23	14	1	07:00:57	06:58:11	21-Mar
24	15	1	07:03:44	07:00:58	21-Mar
25	16	1	07:06:31	07:03:45	21-Mar
26	17	1	07:09:18	07:06:32	21-Mar
27	18	1	07:12:05	07:09:19	21-Mar
28	19	1	07:14:52	07:12:06	21-Mar
29	20	1	07:17:39	07:14:53	21-Mar
30	21	1	07:20:26	07:17:40	21-Mar
31	22	1	07:23:13	07:20:27	21-Mar
32	23	1	07:26:00	07:23:14	21-Mar
33	24	1	07:28:47	07:26:01	21-Mar
34	25	1	07:31:34	07:28:48	21-Mar
35	26	1	07:34:21	07:31:35	21-Mar
36	27	1	07:37:08	07:34:22	21-Mar
37	28	1	07:39:55	07:37:09	21-Mar
38	29	1	07:42:42	07:39:56	21-Mar
39	30	1	07:45:29	07:42:43	21-Mar
40	31	1	07:48:16	07:45:30	21-Mar
41	32	1	07:51:03	07:48:17	21-Mar
42	33	1	07:53:50	07:51:04	21-Mar
43	34	1	07:56:37	07:53:51	21-Mar
44	35	1	07:59:24	07:56:38	21-Mar
45	36	1	08:02:11	07:59:25	21-Mar
46	37	1	08:04:58	08:02:12	21-Mar
47	38	1	08:07:45	08:04:59	21-Mar
48	39	1	08:10:32	08:07:46	21-Mar
49	40	1	08:13:19	08:10:33	21-Mar
50	41	1	08:16:06	08:13:20	21-Mar
51	42	1	08:18:53	08:16:07	21-Mar
52	43	1	08:21:40	08:18:54	21-Mar
53	44	1	08:24:27	08:21:41	21-Mar
54	45	1	08:27:14	08:24:28	21-Mar
55	46	1	08:30:01	08:27:15	21-Mar
56	47	1	08:32:48	08:30:02	21-Mar
57	48	1	08:35:35	08:32:49	21-Mar
58	49	1	08:38:22	08:35:36	21-Mar
59	50	1	08:41:09	08:38:23	21-Mar
60	51	1	08:43:56	08:41:10	21-Mar
61	52	1	08:46:43	08:43:57	21-Mar
62	53	1	08:49:30	08:46:44	21-Mar
63	54	1	08:52:17	08:49:31	21-Mar
64	55	1	08:55:04	08:52:18	21-Mar
65	56	1	08:57:51	08:55:05	21-Mar
66	57	1	09:00:38	08:57:52	21-Mar
67	58	1	09:03:25	09:00:39	21-Mar
68	59	1	09:06:12	09:03:26	21-Mar
69	60	1	09:08:59	09:06:13	21-Mar
70	61	1	09:11:46	09:09:00	21-Mar
71	62	1	09:14:33	09:11:47	21-Mar
72	63	1	09:17:20	09:14:34	21-Mar
73	64	1	09:20:07	09:17:21	21-Mar
74	65	1	09:22:54	09:20:08	21-Mar
75	66	1	09:25:41	09:22:55	21-Mar
76	67	1	09:28:28	09:25:42	21-Mar
77	68	1	09:31:15	09:28:29	21-Mar
78	69	1	09:34:02	09:31:16	21-Mar
79	70	1	09:36:49	09:34:03	21-Mar
Sitaron Ki Degree

Cell Formulas
Range	Formula
X10	X10	=Y10+TIME(,$F$219,$E$219)
Y10	Y10	=X7
Z10	Z10	=K219
X11:X79	X11	=IF(X10<$V$7,Y11+TIME(,$F$219,$E$219),"")
Y11:Y79	Y11	=X10+TIME(,,1)
AA10	AA10	=D219
AA11	AA11	=IF(AND(V10<30,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=30<60,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=60<90,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=90<120,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=120<150,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=150<180,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))=IF(AND(V10>=180<210,AA10=D219),AA10,INDEX($D$219:$D$230,MATCH(AA10,$D$219:$D$230,0)+1))
V10	V10	=W10
V11:V79	V11	=V10+W11

I hope you understand the concept, i did not mention the lookup table because its rage is already given and it will be excessive size to paste here in XL2BB.

The formula is in cell AA11

Thanks & regards.

can you describe the basic format and contents of the lookup table and provide a few rows as an example?

 

[mba_110]

can you describe the basic format and contents of the lookup table and provide a few rows as an example?

To Make it easy, i have revised the main table format, almost of all formulas i have made it work correctly, only issues here is with "Starting sign" which is at I9 and "End time" which is at D9, i have left first row as it is because its starting point and can't be changed, just for your information once formulas are corrected i will drag it to below till the last to get all result for formula should accommodate this action.

MAIN TABLE

Mirza Bahadur Ali.xlsx
	B	C	D	E	F	G	H	I
7	Total Degrees	Degree	End	Start	Sun Set	Sun Raise	Date	Starting Sign
8	1	1	6:25:02	6:22:16	15:47:00	6:22:15	21-Mar	Aries
9	2	2	6:27:49	6:25:03	15:47:00	6:22:15	21-Mar	Aries
10	3	3	6:30:36	6:27:50	15:47:00	6:22:15	21-Mar	Aries
11	4	4	6:33:23	6:30:37	15:47:00	6:22:15	21-Mar	Aries
12	5	5	6:36:10	6:33:24	15:47:00	6:22:15	21-Mar	Aries
13	6	6	6:38:57	6:36:11	15:47:00	6:22:15	21-Mar	Aries
14	7	7	6:41:44	6:38:58	15:47:00	6:22:15	21-Mar	Aries
15	8	8	6:44:31	6:41:45	15:47:00	6:22:15	21-Mar	Aries
16	9	9	6:47:18	6:44:32	15:47:00	6:22:15	21-Mar	Aries
17	10	10	6:50:05	6:47:19	15:47:00	6:22:15	21-Mar	Aries
18	11	11	6:52:52	6:50:06	15:47:00	6:22:15	21-Mar	Aries
19	12	12	6:55:39	6:52:53	15:47:00	6:22:15	21-Mar	Aries
20	13	13	6:58:26	6:55:40	15:47:00	6:22:15	21-Mar	Aries
21	14	14	7:01:13	6:58:27	15:47:00	6:22:15	21-Mar	Aries
22	15	15	7:04:00	7:01:14	15:47:00	6:22:15	21-Mar	Aries
23	16	16	7:06:47	7:04:01	15:47:00	6:22:15	21-Mar	Aries
24	17	17	7:09:34	7:06:48	15:47:00	6:22:15	21-Mar	Aries
25	18	18	7:12:21	7:09:35	15:47:00	6:22:15	21-Mar	Aries
26	19	19	7:15:08	7:12:22	15:47:00	6:22:15	21-Mar	Aries
27	20	20	7:17:55	7:15:09	15:47:00	6:22:15	21-Mar	Aries
28	21	21	7:20:42	7:17:56	15:47:00	6:22:15	21-Mar	Aries
29	22	22	7:23:29	7:20:43	15:47:00	6:22:15	21-Mar	Aries
30	23	23	7:26:16	7:23:30	15:47:00	6:22:15	21-Mar	Aries
31	24	24	7:29:03	7:26:17	15:47:00	6:22:15	21-Mar	Aries
32	25	25	7:31:50	7:29:04	15:47:00	6:22:15	21-Mar	Aries
33	26	26	7:34:37	7:31:51	15:47:00	6:22:15	21-Mar	Aries
34	27	27	7:37:24	7:34:38	15:47:00	6:22:15	21-Mar	Aries
35	28	28	7:40:11	7:37:25	15:47:00	6:22:15	21-Mar	Aries
36	29	29	7:42:58	7:40:12	15:47:00	6:22:15	21-Mar	Aries
37	30	30	7:45:45	7:42:59	15:47:00	6:22:15	21-Mar	Aries
38	31	1	7:48:58	7:45:46	15:47:00	6:22:15	21-Mar	Taurus
39	32	2	7:52:11	7:48:59	15:47:00	6:22:15	21-Mar	Taurus
Sitaron Ki Degree

Cell Formulas
Range	Formula
B8	B8	=C8
D8	D8	=IF(I8='Taleh Burj'!D32,TIME(,'Taleh Burj'!$G$32,'Taleh Burj'!$F$32)+E8)
E8	E8	=G8+TIME(,,1)
F8:F39	F8	=INDEX('Sunraise & SunSet'!$J$12:$J$22,MATCH(H8,'Sunraise & SunSet'!$L$12:$L$22,0))
G8:G39	G8	=INDEX('Sunraise & SunSet'!$K$12:$K$22,MATCH(H8,'Sunraise & SunSet'!$L$12:$L$22,0))
I8	I8	='Burj O Siyarat'!$D$5
B9:B39	B9	=COUNT($C$8:C9)
C9:C39	C9	=IF(C8=30,1,C8+1)
D9:D39	D9	=IF(I9='Taleh Burj'!$D$32,E9+TIME(,'Taleh Burj'!$G$32,'Taleh Burj'!$F$32),IF(I9='Taleh Burj'!$D$33,E9+TIME(,'Taleh Burj'!$G$33,'Taleh Burj'!$F$33),IF(I9='Taleh Burj'!$D$34,E9+TIME(,'Taleh Burj'!$G$34,'Taleh Burj'!$F$34),IF(I9='Taleh Burj'!$D$35,E9+TIME(,'Taleh Burj'!$G$35,'Taleh Burj'!$F$35),IF(I9='Taleh Burj'!$D$36,E9+TIME(,'Taleh Burj'!$G$36,'Taleh Burj'!$F$36),IF(I9='Taleh Burj'!$D$37,E9+TIME(,'Taleh Burj'!$G$37,'Taleh Burj'!$F$37),IF(I9='Taleh Burj'!$D$38,E9+TIME(,'Taleh Burj'!$G$38,'Taleh Burj'!$F$38),IF(I9='Taleh Burj'!$D$39,E9+TIME(,'Taleh Burj'!$G$39,'Taleh Burj'!$F$39),IF(I9='Taleh Burj'!$D$40,E9+TIME(,'Taleh Burj'!$G$40,'Taleh Burj'!$F$40),IF(I9='Taleh Burj'!$D$41,E9+TIME(,'Taleh Burj'!$G$41,'Taleh Burj'!$F$41),IF(I9='Taleh Burj'!$D$41,E9+TIME(,'Taleh Burj'!$G$41,'Taleh Burj'!$F$41),IF(I9='Taleh Burj'!$D$42,E9+TIME(,'Taleh Burj'!$G$42,'Taleh Burj'!$F$42),IF(I9='Taleh Burj'!$D$43,E9+TIME(,'Taleh Burj'!$G$43,'Taleh Burj'!$F$43),IF(I9='Taleh Burj'!$D$44,E9+TIME(,'Taleh Burj'!$G$44,'Taleh Burj'!$F$44)))))))))))))))
E9:E39	E9	=IF(H9=H8,D8+TIME(,,1),G9)
H9:H39	H9	=IF(D8<F8,H8,H8+1)
I9:I39	I9	=IF(C8=30,INDEX('Taleh Burj'!$D$32:$D$43,MATCH(I8,'Taleh Burj'!$D$32:$D$43,0)+1),IF(B8=360,'Taleh Burj'!$D$32,I8))

LOOKUP TABLE

Mirza Bahadur Ali.xlsx
	B	C	D	E	F	G
30	نمبربرج	برج	Stars	برج بمطابق اطراف	شمس فی درجہ قیام
31					سکنڈس(S)	منٹس (M)
32	1	حمل	Aries	مشرقی	46	2
33	2	ثور	Taurus	جنوبی	12	3
34	3	جوزاء	Gemini	مغربی	58	3
35	4	سرطان	Cancer	شمالی	36	4
36	5	اسد	Leo	مشرقی	48	4
37	6	سنبلہ	Virgo	جنوبی	40	4
38	7	ميزان	Libra	مغربی	40	4
39	8	عقرب	Scorpio	شمالی	48	4
40	9	قوس	Sagittarius	مشرقی	36	4
41	10	جدّي	Capricorn	جنوبی	58	3
42	11	دلو	Aquarius	مغربی	12	3
43	12	حُوت	Pisces	شمالی	46	2
Taleh Burj

FORMULA # 01 (I:9 - Need to Correct as per below requirement)
In above table, when value at B column reaches 360 then it has to start looking up the value from beginning of list which is referenced in look up table, in look up table as you can see there are 12 rows and each row absorb 30 in main table so 30 X 12 = 360 once my formula reach 360 at column B it's giving me error because there is no further value after row 12 in lookup table, in order to avoid this i want formula to start again the lookup value from D:32 of lookup table once it reaches 360 at column B in main table. I have added another if condition in I9 but it still gave me error once B column value reaches 360 need help to fix this issue.


FORMULA # 02 (D:9 - Need to Correct as per below requirement)
In main table sunset time is mentioned in each row, i want formula to cut of the End time at D:9 exactly to sun set time once it greater than Sun set time, end time also has a formula which takes the values from lookup table based on "Stars" duration

for example Sunset time (17:45:05) End Time (17:47:15) Then i need End Time should be 17:45:05 adjusting/limiting the End time to 17:45 Sunset time, because from next row new sun raise and sun set time will be calculated.