Lookup and Match question.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GTVT06 - 03 Jul 2007 03:26 GMT

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

Reply to this Message

T. Valko - 03 Jul 2007 05:39 GMT

Try this:

Assume your table is in the range A2:D7

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:D$7),0)),"",INDEX(D$2:D$7,MATCH(1,(A$2:A$7=A2)*(B$2:B$7=D$2:D$7),0)))

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

Signature

Biff
Microsoft Excel MVP

> Hello, I was wondering if someone could assist me.
> I tried figuring this out with a Index and Match formula but cant get
[quoted text clipped - 32 lines]
> 14:30
> JaneD567 14:30 Lunch 18:30 14:30

Reply to this Message

GTVT06 - 03 Jul 2007 13:27 GMT

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.

Reply to this Message

T. Valko - 03 Jul 2007 19:35 GMT

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.

Signature

Biff
Microsoft Excel MVP

> 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.

Reply to this Message

GTVT06 - 03 Jul 2007 20:14 GMT

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.

Reply to this Message

T. Valko - 03 Jul 2007 21:08 GMT

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

Signature

Biff
Microsoft Excel MVP

> Sorry about that,
> I found the problem! I was entering it as an array formula (CNTRL+Shift
[quoted text clipped - 3 lines]
> both H:MM it worked like a charm!!!! Thanks a bunch! sorry about the
> confusion.

Reply to this Message