vlookup help

Amine008

New Member
Joined
Dec 17, 2016
Messages
14
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
BadgeFrom DateTo DateAttendanceBadgeDateAttendance
123416/12/201619/12/2016Absent123417/12/2016Here I want to see if badge id is present or off this day
123519/12/201620/12/2016Holiday123517/12/2016Here I want to see if badge id is present or off this day
123620/12/201621/12/2016Holiday123617/12/2016Here I want to see if badge id is present or off this day
123721/12/201622/12/2016Absent123717/12/2016Here I want to see if badge id is present or off this day
123822/12/201623/12/2016sick123817/12/2016Here I want to see if badge id is present or off this day
123418/12/2016Here I want to see if badge id is present or off this day
123518/12/2016Here I want to see if badge id is present or off this day
123618/12/2016Here I want to see if badge id is present or off this day
123718/12/2016Here I want to see if badge id is present or off this day
123818/12/2016Here 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>
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Something like this (in I2)?
Code:
=IF(AND(H2>=B2,H2<=C2),D2,"In")
 
Last edited:

Amine008

New Member
Joined
Dec 17, 2016
Messages
14
Something like this (in I2)?
Code:
=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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,639
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

New Member
Joined
Dec 17, 2016
Messages
14

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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Do your date fields also contain times by any chance? Format them as dd/mm/yyyy hh:mm:ss to check.
 
Last edited:

Amine008

New Member
Joined
Dec 17, 2016
Messages
14

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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,639
... 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

New Member
Joined
Dec 17, 2016
Messages
14
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,639
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top