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

user_18587422 0
Viewed 117

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.

formula excel-formula excel
1 Answers

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.

user_97581940
Related