Govind,

It is not working. The text in columns K and O are not being brought
across.

Thanks,

Roy

"Govind" wrote:

Hi,

Its not explicitly mentioned but are worked out by the formula. But my
reference wasnt correct in my earlier formula and hence use this

=OFFSET(J1,MATCH(1,(J1:J100=AF6)+(T1:T100=AF6))-1,1)&OFFSET(J1,MATCH(1,(J1:J100=AF6)+(T1:T100=AF6) )-1,5)

The offset formula starts in column J, matches the given date, goes that
row and then moves 1 column right to refer to column K in the first
formula. In the second one, it moves 5 column right to refer to column
O. Change J1:J100 and T1:T100 to the actual range.



Regards

Govind.



roy.okinawa wrote:

I don't see any reference to Columns K or O in the formula. Is this formula
going to search for the text in those columns?

Roy



"Govind" wrote:


Hi Roy,

Try this formula

=OFFSET(J1,MATCH(1,(J1:J100=AF6)+(T1:T100=AF6) )-1,2)&OFFSET(J1,MATCH(1,(J1:J100=AF6)+(T1:T100=AF6) )-1,3)

entered with CTRL+SHIFT+ENTER.

Here J1:J100 is the range with date opened and T1:T100 is the range with
date closed. Change the formula to suit your actual range.

Regards

Govind.


roy.okinawa wrote:


Govind,

The date will never be the same in column J and T.

Roy

"Govind" wrote:



Hi,

What if the same date is there in Column J and Column T? How do you
want your formula to work in that case?

Regards

Govind.



roy.okinawa wrote:


Here is what I have:

Column J is the date opened. Column T is the date closed. Of course,
column T will have no date if still open.
Column K and O may or may not have text.
Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the
search.

What I need is a formula in AF8 that will look at the date entered in AF6
and find a match in columns J and T. Then it will look to see if there is
text in K and O. If so, populate AF8 with that text.