Lookup and Match question.

[GTVT06]

Hello, I was wondering if someone could assist me.
I tried figuring this out with a Index and Match formula but cant get
it to work. Using the example below, I'm trying to put a formula in
Column E that will let me know if there is anything scheduled for an
agent in column D that matches the time in column B and if so place
the time from column D into the cell in E else leave it blank.
The list of agents and amount of rows each agent will have, will vary
from day to day.

Example layout
A B C
D E
Agent Shift Start Availability Avail Start Formula
in this column
JohnD345 13:00 Meeting 13:00
JohnD345 13:00 Break 15:45
JohnD345 13:00 Lunch 18:00
JaneD567 14:30 Training 14:30
JaneD567 14:30 Break 16:30
JaneD567 14:30 Lunch 18:30

Would like end result to look like

A B C
D E
Agent Shift Start Availability Avail Start Formula
in this column
JohnD345 13:00 Meeting 13:00 13:00
JohnD345 13:00 Break 15:45 13:00
JohnD345 13:00 Lunch 18:00
13:00
JaneD567 14:30 Training 14:30
14:30
JaneD567 14:30 Break 16:30
14:30
JaneD567 14:30 Lunch 18:30 14:30

 

[T. Valko]

Try this:

Assume your table is in the range A27

Enter this array formula** in E2 and copy down as needed:

=IF(ISNA(MATCH(1,(A$2:A$7=A2)*(B$2:B$7=D$2$7),0)),"",INDEX(D$2$7,MATCH(1,(A$2:A$7=A2)*(B$2:B$7=D$2$7),0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[GTVT06]

Thanks but I cant seem to get that formula to work, but I been playing
around with it, and I will continue to try and get it to work.

 

[T. Valko]

Here's a screencap to demonstrate that the formula does work:

http://img528.imageshack.us/img528/3392/arrayse0.jpg

Notice the formula is enclosed in { } brackets. These brackets mean the
formula is an array formula. An array formula *must* be entered using the
key combination of CTRL,SHIFT,ENTER (not just ENTER). That is, hold down
both the CTRL key and the SHIFT key then hit ENTER. If done properly Excel
will add the { } brackets. You can't just type these brackets in, you *must*
use the key combo.

 

[GTVT06]

Sorry about that,
I found the problem! I was entering it as an array formula (CNTRL+Shift
+Enter) what the problem was, was that the time formats were
different. one was formated m/dd/yyyy h:mm:ss AM/PM where as the
other was simply formated as H:MM After reformating it so that their
both H:MM it worked like a charm!!!! Thanks a bunch! sorry about the
confusion.

 

[T. Valko]

Glad you got it straightened out. Thanks for the feedback!