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

Question

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.

Answer 1

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():

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:

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.