VLookup with multi-condition

[anthony alvarez]

I am doing a vlookup with regard to time keeping



A B C D

ID Date Time Status

0001 01/01/2010 7:25AM IN

0002 01/01/2010 8:12AM IN

0001 01/01/2010 5:15PM OUT

0002 01/01/2010 6:45PM OUT



I want to have an out put like





Date Time IN Time OUT

01/01/2010 7:25AM 5:15PM



Can anybody help me please.. Best regards,Anthony


Submitted via EggHeadCafe - Software Developer Portal of Choice
XCeed Streaming Compression Library
http://www.eggheadcafe.com/tutorial...f-769ec7085cf2/xceed-streaming-compressi.aspx

 

[Archimedes' Lever]

I am doing a vlookup with regard to time keeping



A B C D

ID Date Time Status

0001 01/01/2010 7:25AM IN

0002 01/01/2010 8:12AM IN

0001 01/01/2010 5:15PM OUT

0002 01/01/2010 6:45PM OUT



I want to have an out put like





Date Time IN Time OUT

01/01/2010 7:25AM 5:15PM



Can anybody help me please.. Best regards,Anthony

http://office.microsoft.com/search/redir.aspx?assetid=TC300083091033&respos=14&pid=CT101172771033

 

[T. Valko]

One way...

Assuming your table data is in the range A25

Lookup values:

C10 = In
D10 = Out
A11 = 0001
B11 = 1/1/2010

Enter this array formula** in C11 and copy across to D11:

=INDEX($C$2:$C$5,MATCH(1,($A$2:$A$5=$A11)*($B$2:$B$5=$B11)*($D$2:$D$5=C$10),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Time (if your times in the table are true Excel time values).