Find the the value with multiple condition

[Abdulkhadar]

Dear Excel Experts,
I want to find the value according to the date from multiple columns.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	01/01/1987	01/07/1993	01/04/1998	01/07/2005				My Goal is
2	780	840	2500	4800					840
3	795	855	2550	4900				01/03/1988	855
4	810	870	2600	5000				01/03/1989	870
5	825	885	2650	5100				01/04/1990	885
6	840	900	2700	5200				01/03/1993	900
7	855	920	2775	5300				01/07/1993	980
8	870	940	2850	5400				01/03/1994	1000
9	885	960	2925	5500				01/03/1995	1020
10	900	980	3000	5600				01/03/1996	1040
11	920	1000	3075	5700				01/04/1998	3225
12	940	1020	3150	5800				01/03/1999	3300
13	960	1040	3225	5900				01/03/2000	3375
14	980	1060	3300	6000				01/03/2001	3450
15	1000	1080	3375	6125				01/07/2005	6250
16	1020	1100	3450	6250				01/03/2006	6375
17	1040	1130	3550	6375				01/04/2007	6500
18	1060	1160	3650	6500						so on
19	1080	1190	3750	6650
20	1100	1220	3850	6800
21
Sheet3

Cell Formulas
Range	Formula
I3:I6	I3	=LOOKUP(H3,$A$1:$D$1,INDEX(A:A,MATCH(I2,A:A,0)+1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D9	Cell Value	duplicates	text	NO
C2:C9	Cell Value	duplicates	text	NO
B2:B8	Cell Value	duplicates	text	NO
A2:A9	Cell Value	duplicates	text	NO

Thanks in advance

 

[kevin9999]

Maybe like this?

Book1
	A	B	C	D	E	F	G	H	I	J
1	1/01/1987	1/07/1993	1/04/1998	1/07/2005				My Goal is
2	780	840	2500	4800					840
3	795	855	2550	4900				1/03/1988	855
4	810	870	2600	5000				1/03/1989	870
5	825	885	2650	5100				1/04/1990	885
6	840	900	2700	5200				1/03/1993	900
7	855	920	2775	5300				1/07/1993	980
8	870	940	2850	5400				1/03/1994	1000
9	885	960	2925	5500				1/03/1995	1020
10	900	980	3000	5600				1/03/1996	1040
11	920	1000	3075	5700				1/04/1998	3225
12	940	1020	3150	5800				1/03/1999	3300
13	960	1040	3225	5900				1/03/2000	3375
14	980	1060	3300	6000				1/03/2001	3450
15	1000	1080	3375	6125				1/07/2005	6250
16	1020	1100	3450	6250				1/03/2006	6375
17	1040	1130	3550	6375				1/04/2007	6500
18	1060	1160	3650	6500						so on
19	1080	1190	3750	6650
20	1100	1220	3850	6800
Sheet3

Cell Formulas
Range	Formula
I3:I6	I3	=LOOKUP(H3,$A$1:$D$1,INDEX(A:A,MATCH(I2,A:A,0)+1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D20	Expression	=COUNTIFS($H$3:$H$17,A$1,$I$3:$I$17,A2)>0	text	YES

 

[Abdulkhadar]

Maybe like this?

Thanks for your quick reply sir, but this formula is not working as per my criteria. I want a formula only at I3 cell to drag

Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	01/01/1987	01/07/1993	01/04/1998	01/07/2005				My Goal is
2	780	840	2500	4800					840
3	795	855	2550	4900				01/03/1988	855
4	810	870	2600	5000				01/03/1989	870
5	825	885	2650	5100				01/04/1990	885
6	840	900	2700	5200				01/03/1993	900
7	855	920	2775	5300				01/07/1993	980
8	870	940	2850	5400				01/03/1994	1000
9	885	960	2925	5500				01/03/1995	1020
10	900	980	3000	5600				01/03/1996	1040
11	920	1000	3075	5700				01/04/1998	3225
12	940	1020	3150	5800				01/03/1999	3300
13	960	1040	3225	5900				01/03/2000	3375
14	980	1060	3300	6000				01/03/2001	3450
15	1000	1080	3375	6125				01/07/2005	6250
16	1020	1100	3450	6250				01/03/2006	6375
17	1040	1130	3550	6375				01/04/2007	6500
18	1060	1160	3650	6500						so on
19	1080	1190	3750	6650
20	1100	1220	3850	6800
Sheet3

 

[Peter_SSs]

So, what is wrong with your existing formula in I3 given that you have a heading above that section that says "My goal is"?

Assuming that your formulas are not doing what you want
- remove them
- manually enter the results that you do want
- explain in words how you get the first 2 or 3 of those manually entered results.

 

[Abdulkhadar]

So, what is wrong with your existing formula in I3 given that you have a heading above that section that says "My goal is"?

Assuming that your formulas are not doing what you want
- remove them
- manually enter the results that you do want
- explain in words how you get the first 2 or 3 of those manually entered results.

sorry for my mistake. before deleting the formula I posted it. then I removed the formula and post again No. #3 . my formula work only for column A, but I want from Colum A to D. My Goal is, I want values from A2 to D20 as per mentioned Date in Column H. i.e., if date from 01/07/1987 to 30/06/1993 in column H then, look the values from Column A. if date from 01/07/1993 to 31/03/1998 in column H then took values from Column B. like this... Once again sorry for my mistake.

 

[Peter_SSs]

I removed the formula and post again No. #3

Oops, sorry, somehow I missed post #3

Try this

23 08 05.xlsm
	A	B	C	D	E	F	G	H	I
1	1/01/1987	1/07/1993	1/04/1998	1/07/2005				My Goal is
2	780	840	2500	4800					840
3	795	855	2550	4900				1/03/1988	855
4	810	870	2600	5000				1/03/1989	870
5	825	885	2650	5100				1/04/1990	885
6	840	900	2700	5200				1/03/1993	900
7	855	920	2775	5300				1/07/1993	980
8	870	940	2850	5400				1/03/1994	1000
9	885	960	2925	5500				1/03/1995	1020
10	900	980	3000	5600				1/03/1996	1040
11	920	1000	3075	5700				1/04/1998	3225
12	940	1020	3150	5800				1/03/1999	3300
13	960	1040	3225	5900				1/03/2000	3375
14	980	1060	3300	6000				1/03/2001	3450
15	1000	1080	3375	6125				1/07/2005	6250
16	1020	1100	3450	6250				1/03/2006	6375
17	1040	1130	3550	6375				1/04/2007	6500
18	1060	1160	3650	6500
19	1080	1190	3750	6650
20	1100	1220	3850	6800
Lookup

Cell Formulas
Range	Formula
I3:I17	I3	=INDEX(A$2:D$20,MATCH(I2,INDEX(A$2:D$20,0,MATCH(IF(H2="",H3,H2),A$1:D$1)))+(MATCH(H3,A$1:D$1)=MATCH(IF(H2="",H3,H2),A$1:D$1)),MATCH(H3,A$1:D$1))

 

[Abdulkhadar]

Oops, sorry, somehow I missed post #3

Try this

Wow great job. thanks for your quick reply.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.