vlookup help

[Amine008]

Hey guys,

I'm new here in your forum and need a quick help to solve one issue that I'm facing.
In the below, I want a formula to see if the badge id was present in the date (Culmn H) or off in this date.

Thanks for your help.

A B C D G H I

Badge	From Date	To Date	Attendance		Badge	Date	Attendance
1234	16/12/2016	19/12/2016	Absent			1234	17/12/2016	Here I want to see if badge id is present or off this day
1235	19/12/2016	20/12/2016	Holiday			1235	17/12/2016	Here I want to see if badge id is present or off this day
1236	20/12/2016	21/12/2016	Holiday			1236	17/12/2016	Here I want to see if badge id is present or off this day
1237	21/12/2016	22/12/2016	Absent			1237	17/12/2016	Here I want to see if badge id is present or off this day
1238	22/12/2016	23/12/2016	sick			1238	17/12/2016	Here I want to see if badge id is present or off this day
						1234	18/12/2016	Here I want to see if badge id is present or off this day
						1235	18/12/2016	Here I want to see if badge id is present or off this day
						1236	18/12/2016	Here I want to see if badge id is present or off this day
						1237	18/12/2016	Here I want to see if badge id is present or off this day
						1238	18/12/2016	Here I want to see if badge id is present or off this day

<colgroup><col><col span="2"><col span="4"><col><col></colgroup><tbody>
</tbody>

 

[Ruddles]

Something like this (in I2)?

=IF(AND(H2>=B2,H2<=C2),D2,"In")

 

[Amine008]

Something like this (in I2)?

=IF(AND(H2>=B2,H2<=C2),D2,"In")

Thanks
for your reply. Yes its like this but I want he look also at the badge to be able to see the number of presence between the 2 dates.

 

[Tetra201]

Is this what you need?

=IF(AND(H2>=INDEX(B:B,MATCH(G2,A:A,0)),H2<=INDEX(C:C,MATCH(G2,A:A,0))),INDEX(D:D,MATCH(G2,A:A,0)),"Present")

 

[Amine008]

ADVERTISEMENT

Is this what you need?

=IF(AND(H2>=INDEX(B:B,MATCH(G2,A:A,0)),H2<=INDEX(C:C,MATCH(G2,A:A,0))),INDEX(D:D,MATCH(G2,A:A,0)),"Present")

Yes
Yes Yes thanks a lot for your help and support. Appreciate.
One more help please If I change the date in culmn H2 to 16/12/2016 I see in Culmn I2 present. the start date for Absent in the second row is 16/12/2016

 

[Ruddles]

Do your date fields also contain times by any chance? Format them as dd/mm/yyyy hh:mm:ss to check.

 

[Amine008]

ADVERTISEMENT

Do your date fields also contain times by any chance? Format them as dd/mm/yyyy hh:mm:ss to check.

yes
The format in the excel sheet is 16/12/2016 12:05:10

 

[Tetra201]

... If I change the date in culmn H2 to 16/12/2016 I see in Culmn I2 present. the start date for Absent in the second row is 16/12/2016

The format in the excel sheet is 16/12/2016 12:05:10

So, Badge 1234 was present on 16/12/2016 until 12:05, and was absent after that time.
How do you want this to be reflected in column I?

 

[Amine008]

So, Badge 1234 was present on 16/12/2016 until 12:05, and was absent after that time.
How do you want this to be reflected in column I?

yes.
Can we eliminate the time ? Column I should be absent not present

 

[Tetra201]

Can we eliminate the time ? Column I should be absent not present

Yes -- by adding two INT's. Here is an updated formula:

=IF(AND(H2>=INT(INDEX(B:B,MATCH(G2,A:A,0))),H2<=INT(INDEX(C:C,MATCH(G2,A:A,0)))),INDEX(D:D,MATCH(G2,A:A,0)),"Present")