Search code examples
excelexcel-formulaformula

Find the newest item in a 2 columns predecessor-successor list

I have two columns in Excel with phase-out and phase-in items:

Old item New item
AAAAAAAAA BBBBBBBBB
GGGGGGGGG DDDDDDDDDD
BBBBBBBBB KKKKKKKKKK
KKKKKKKKK CCCCCCCCCC

I'm trying to write a formula that when I enter an item code in another cell, it returns the most recent one.

Example: If I insert AAAAAAAAA it should return CCCCCCCCC, or If I write GGGGGGGGG it should return: DDDDDDDDD.

I tried using the MATCH function and VLOOKUP but it turned up nothing.

Solution

  • I see you haven't got any answer just yet, so let me clarify my comment. As I see it, you need some form of recursion; a mechanic perfectly suited for LAMBDA() which can call itself. MS designed a usefull helper function called REDUCE():

    enter image description here

    The formula in E2:

    =LET(X,$A$2:$A$5,Y,$B$2:$B$5,REDUCE(D2,X,LAMBDA(a,b,FILTER(Y,X=a,a))))

    Or, if you want to calculate the same for a whole range of items to look for:

    enter image description here

    Formula in E2:

    =BYROW(D2:D3,LAMBDA(Z,LET(X,A2:A5,Y,B2:B5,REDUCE(Z,X,LAMBDA(a,b,FILTER(Y,X=a,a))))))

    Important Note:

    REDUCE() will run untill the very end of the matrix given in the 2nd parameter. For a very extensive array this can take much more processing time then necessarily needed. Imagine running 10k lines when there is only 1 new item found in the 2nd line of data. Therefor, you could also write your own recursive function. This can be done in two ways:

    • A named function in the manager to call in a cell;
    • Through the use of LET().

    This 2nd option has my personal preverence, for example in E2:

    =LET(NEWEST,LAMBDA(ME,TXT,IF(COUNTIF(A$2:A$5,TXT),ME(ME,XLOOKUP(TXT,A$2:A$5,B$2:B$5,,0)),TXT)),NEWEST(NEWEST,D2))

    Though much more verbose, this could take much less resources depending on your data as it would stop the recursive call upon when no more new value could be found.