Displaying the last value corresponding to the last occurrence of a combination

Hi all!!

So I have two Columns (B and C) which combined make 1 name and have a value associated to them (which changes every time the combination occurs).

I need to check what was the previous value for the combination so I can see whether the newer occurrence was higher or lower.

I think I got part of the problem, as you can see in the attachment, but I need it to display just the previous one, and not keep on adding.

ExampleOccurances.xlsx

By the way, the table is much bigger than this and will continually grow.

Thanks so much.

Using your posted workbook...
This regular formula returns the previous Value for the Name/Line combination

Please Login or Register  to view this content.

Is that something you can work with?

Originally Posted by kiwinho

Do you think you could walk me through it? What part of the function does what, so I can try and use and tweak it some other time?

I'll do my best...

This formula:
=IF([@Recurrence]=1,"NA",IF(COUNTIFS($B$1:B2,[@Name],$C$1:C2,[@Line])>1,INDEX($E:$E,MAX(INDEX(($B$1:B1&"_"&$C$1:C1=[@Name]&"_"&[@Line])*ROW($B$1:B1),0))),0))
discribes this way:
• If this is the first recurrence:
IF([@Recurrence]=1,"NA",
.......return: NA
Otherwise...
• If the Name/Line combination exists above the current row:
IF(COUNTIFS($B$1:B2,[@Name],$C$1:C2,[@Line])>1,
...return the Value from the last previous instance:
INDEX($E:$E,MAX(INDEX(($B$1:B1&"_"&$C$1:C1=[@Name]&"_"&[@Line])*ROW($B$1:B1),0)))
...Otherwise...return zero:
,0)


This is the interesting part:
MAX(INDEX(($B$1:B1&"_"&$C$1:C1=[@Name]&"_"&[@Line])*ROW($B$1:B1),0))

This section:
($B$1:B1&"_"&$C$1:C1=[@Name]&"_"&[@Line])*ROW($B$1:B1)
returns an array of Row_Numbers and 0's.
If the row's Name/Line combo matches the combo on the line containing the formula
...the row_number is multiplied by 1
Otherwise by zero.

Technically, this part: ($B$1:B1&"_"&$C$1:C1=[@Name]&"_"&[@Line])
returns a series of TRUE or FALSE values.
However, when used in an arithmetic formula,
TRUE becomes 1 and FALSE becomes 0.

So, out of that series of 0's and row_numbers...the largest (MAX) row_number represents the last previous instance of the Name/Line combination.

Now that we have the row number, we use it in the outer INDEX to find that row in column E.

Of particular interest is the INDEX function inside the MAX function.
Without it, you'd need complete the formula by holding down CTRL and SHIFT when you press ENTER
...instead of just pressing ENTER (to create an ARRAY FORMULA).
Since nobody I work with EVER remembers to C+S+E, the INDEX function evaluates the returned values as an array without C+S+E.

I hope that helps.