3

Let's say I have a table with three columns (order is important and can't be changed), rows are sorted by A:

   A                B            C
      date              ID     last_date
10/05/2015        29A06E95     
10/08/2015        E5A884C4
10/12/2015        D24F4975
10/12/2015        D24F4976
10/21/2015        29A06E95

For any row, if the ID also shows up in any row above the current, I want to populate column C with the most recent date (or, since the rows are sorted by A, the date value from the previous row with the same ID). For example, here the result would look like this:

   A                B            C
      date              ID      prev_date
10/05/2015        29A06E95        <blank>
10/08/2015        E5A884C4        <blank>
10/12/2015        29A06E95     10/05/2015
10/12/2015        D24F4976        <blank>
10/21/2015        29A06E95     10/12/2015

How do I do this (I haven't been able to use either MATCH or VLOOKUP since the former finds the first match and the latter wants the search value in the first column)?

Also, I've seen solutions with array formulas, but these don't seem to work within a data table context.

3

This works starting from Excel 2007. Select C3 and put this formula in it:

=IFERROR(INDIRECT("A"&MAX(ROW(B$2:B2)*(B$2:B2=B3))),"")

Confirm formula by ctrl+shift+enter.

Drag (or copy) down to where needed.

  • I did mention in my question that array formulas don't work in a table context. Excel says: "multi-cell array formulas are not allowed in tables" – Dmitry B. Jan 21 '16 at 22:21
  • Made a test. Works smoothly, you don't even need to drag it down; I just deleted C2, as it makes no sense. Am I missing something? – SΛLVΘ Jan 21 '16 at 22:50
  • whenever I try to enter an array formula by using ctrl+shift+enter inside of a data table, Excel is refusing to accept it with the following error: "multi-cell array formulas are not allowed in tables." And yeh, no VBA (at least not yet). – Dmitry B. Jan 21 '16 at 23:00
  • Ok, now I see what you are doing. You are trying to insert a formula having a range selected. That is not what I wrote. Please follow instructions, I tried to make them clearer. – SΛLVΘ Jan 21 '16 at 23:08
0

Can you not just create a second sheet with the columns and dates reversed? Because then the solutions is a simple vlookup.

  • The actual table is complex enough that this is not a preferred solution (i.e. one of the columns is the next date). Actually, no, VLOOKUP still won't work because the key column isn't the first one. MATCH will work. – Dmitry B. Jan 21 '16 at 21:34
0

I think this should do it:

=IFERROR(IF(A2=INDEX(MIN($A$2:$A$6),MATCH(B2,$B$2:$B$6,0)),"",INDEX(MIN($A$2:$A$6),MATCH(B2,$B$2:$B$6,0))),"")

Place this in C2, edit the ranges as necessary, enter as an array (with CTRL+SHIFT+ENTER) and drag down.

Edit: Okay nevermind, apparently arrays aren't working? Can you clarify what you mean by "table context"? AFAIK Array formulas should still work with tables, no?

  • @DmitryB. - thanks for that, but I don't see where it says you can't use arrays. Hm, I'll see if I can get this another way. I assume you do not want a VBA solution? – BruceWayne Jan 21 '16 at 22:59
  • 1
    That was just a link to define what a (data) table is. Whenever I try to add an array formula within a data table, Excel refuses with this message: "multi-cell array formulas are not allowed in tables." And, yeh, no VBA. – Dmitry B. Jan 21 '16 at 23:02
  • 1
    @DmitryB.- not to keep being trivial, but you're entering the formula in a single cell, correct? Not selecting a range, entering the formula, and then hitting CTRL+SHIFT+ENTER, yeah? – BruceWayne Jan 21 '16 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.