Another table lookup question
 

This problem is a little difficult to explain in words, so please
excuse me if this doesn't make sense as is.

I have a large data table with a header row and data something like
this:

Item Event Group Date1
Date2 Days
....
43445 22123 10356 12-Dec-07
....
43448 10356 18-
Dec-07
....

The table is sorted on the "Item" column which contains distinct
values. Each row will have an "Event" value, unless (like this
example) it has a "Group" value, in which case only one of the grouped
Items will have an Event value. The row with the Event value will
always have a Date1 value. The Date2 value is always present, but
with Group-ed Items, it will not always appear on the same row as the
Date1 value.

What I need to do is put a formula in the "Days" column that subtracts
"Date1" from "Date2". For ungrouped Items, there's no problem. But
for grouped Items, I need to return Date1 from the only other row in
the table with a matching "Group" number.

Is this possible without VBA? I'd sure appreciate your ideas on this!

Thanks,
Terry

Another table lookup question
 

Another table lookup question
 

Thanks for this, smartin. It certainly does what I need it to do.

Regards,
Terry

On May 1, 3:34*pm, smartin wrote:
wrote:
This problem is a little difficult to explain in words, so please
excuse me if this doesn't make sense as is.

I have a large data table with a header row and data something like
this:

Item * * * * *Event * * * * *Group * * * * *Date1
Date2 * * * * *Days
...
43445 * * * 22123 * * * *10356 * * * * *12-Dec-07
...
43448 * * * * * * * * * * * * *10356 * * * * * * * * * * * * * * *18-
Dec-07
...

The table is sorted on the "Item" column which contains distinct
values. *Each row will have an "Event" value, unless (like this
example) it has a "Group" value, in which case only one of the grouped
Items will have an Event value. *The row with the Event value will
always have a Date1 value. *The Date2 value is always present, but
with Group-ed Items, it will not always appear on the same row as the
Date1 value.

What I need to do is put a formula in the "Days" column that subtracts
"Date1" from "Date2". *For ungrouped Items, there's no problem. *But
for grouped Items, I need to return Date1 from the only other row in
the table with a matching "Group" number.

Is this possible without VBA? *I'd sure appreciate your ideas on this!

Thanks,
Terry

I think I follow you.

I put your sample data in A1:F3.

On "grouped" rows, (cell F3) this will find the corresponding Date1 from
another row and do the subtraction. This is an array formula -- commit
with Ctrl+Shift+Enter (not just Enter):

* *=E3-MAX(IF(C3=$C$2:$C$9,$D$2:$D$9,0))