If MIN OFFSET

[Russle]

Hi All,

I am trying to return a comment based on the oldest date;

Example attached;
I would want to find the oldest date that is "B" in column B and has no date in column D. I then want to pull across the comment that is in Column C.

I have tried using an offset If min formula without any luck, can anyone assist?

Thanks,

[Tony Valko]

Try this array formula**:

=INDEX(C2:C11,MATCH(MAX(IF((B2:B11="B")*(D2:D11=""),A2:A11)),IF((B2:B11="B")*(D2:D11=""),A2:A11),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.

[alansidman]

@Biff
I think that the Max should be a Min. OP is looking for Oldest date. Nice formula in spite of this.

Alan

[Tony Valko]

Ok, so try one or the other...

The MAX version in post #2 for the most recent date or this MIN version for the earliest date.

Try this array formula**:

=INDEX(C2:C11,MATCH(MIN(IF((B2:B11="B")*(D2:D11=""),A2:A11)),IF((B2:B11="B")*(D2:D11=""),A2:A11),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.

[Russle]

Changed the max to min and worked perfectly thanks.

Makes sense now I've seen the formula

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[Hallowedddd]

Mr. Valko,
You are really excel magician.
This is one of the craziest formula I have ever seen
Thank you very much !

All The Best Mr. Valko