Search for, and extract data

[MIKEBIZ]

​

	A	B	C	D	E	F	G	H	I
1	TODAY'S DATE:	WITH/WITHOUT PAY:					DATE EMPLOYEE LEAVING	DATE EMPLOYEE RETURNING	WITH OR WITHOUT PAY
2	11-9-2015						10-30-2015	11-2-2015	WITH
3							11-7-2015	11-14-2015	WITHOUT
4							12-25-2015	12-26-2015	BIRTHDAY PAY

<tbody>
</tbody>

HELLO, WHAT I'M TRYING TO DO IS WRITE A FORMULA IN CELL B2 THAT WILL CHECK IF TODAY'S DATE (A2) IS >= DATE EMPLOYEE LEAVING (COLUMN G) AND AT THE SAME TIME
A2 < DATE EMPLOYEE RETURNING (COLUMN H). IF BOTH CONDITIONS ARE TRUE, I WOULD LIKE IT TO RETURN THE VALUE FROM COLUMN I. IF ONE OF THE CONDITIONS IS NOT TRUE,THEN "". THANK YOU IN ADVANCE FOR YOUR HELP, THIS PROBLEM IS DRIVING ME CRAZY

 

[JoeMo]

Copy B2 down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	TODAY'SDATE:	WITH/WITHOUT PAY:					DATE EMPLOYEE LEAVING	DATE EMPLOYEE RETURNING	WITH OR WITHOUT PAY
2	11/9/2015						10/30/2015	11/2/2015	WITH
3	11/9/2015	WITHOUT					11/7/2015	11/14/2015	WITHOUT
4	11/9/2015						12/25/2015	12/26/2015	BIRTHDAY PAY
Sheet1

 

[MIKEBIZ]

thanks for the suggestion, but that's not what I'm looking for exactly. The data in G,H,and I is dynamic and could be 1000 rows long. I need B2 to change as the date in A2 changes...

 

[JoeMo]

thanks for the suggestion, but that's not what I'm looking for exactly. The data in G,H,and I is dynamic and could be 1000 rows long. I need B2 to change as the date in A2 changes...

Well you could copy the formula down to the last row of data. B2 will change if you set the date in A2 to be inside your target range (10/30/2015 - 11/2/2015). If that's not what you want, maybe you could elaborate a bit.

 

[MIKEBIZ]

Again thanks for your help... I guess i'm not explaining my problem well enough. i would like the formula to search both columns G and H, find the row where A2 => G and A2 < H, then return the value from I in that row.

I guess I need an INDEX MATCH formula that considers those two conditions.

 

[JoeMo]

Again thanks for your help... I guess i'm not explaining my problem well enough. i would like the formula to search both columns G and H, find the row where A2 => G and A2 < H, then return the value from I in that row.

I guess I need an INDEX MATCH formula that considers those two conditions.

So the only input date is the lone value in A2 and the match is to be returned to B2 ...... or ????

 

[MIKEBIZ]

yes, exactly!!!

This sheet is part of a bigger workbook, The date in A2 will change and I need the match to be returned to B2

 

[MIKEBIZ]

So, the formula in B2 should check if the date in A2 falls in between G and H. Then if so, return I

 

[JoeMo]

So, the formula in B2 should check if the date in A2 falls in between G and H. Then if so, return I

Does this do it?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	TODAY'SDATE:	WITH/WITHOUT PAY:					DATE EMPLOYEE LEAVING	DATE EMPLOYEE RETURNING	WITH OR WITHOUT PAY
2	11/9/2015	WITHOUT					10/30/2015	11/2/2015	WITH
3							11/7/2015	11/14/2015	WITHOUT
4							12/25/2015	12/26/2015	BIRTHDAY PAY
Sheet1

 

[MIKEBIZ]

Not realy, its exactly what Im looking for but it keeps giving me #ref error