Tracking variable name changes within group in Excel

Question

I am annually tracking the names of car models, which may or may not change each year (its random).

Suppose I start with three models in 2023 that all changes name in 2024 and 2025, thus producing the columns "Year", "Old_name", and "New_name" in the dataset below. E.g., a model is renamed from "A" to "B" in 2023, then renamed from "B" to "C" in 2024, and finally from "C" to "D" in 2025 ("A" through "D" is thus the same model).

I would like to produce a column ("Naming_group") that tracks the change over time, and then gives it the newest name that the model currently has. E.g., "D" is returned for the model that was once called "A", "B", and "C".

I have tried a combination of =IFERROR(MATCH([@[Old_name]],[New_name],0),"") and

=LET(
    data,UNIQUE(SORT(VSTACK([Match_old],[Match_new]))),
    match,MAX(FILTER(data,data<>"")),

    INDEX([New_name],match)
)

But that only works if there is only one model in the dataset.

Year	Old_name	New_name	Naming_group
2023	A	B	D
2023	H	I	K
2023	V	X	Z
2024	B	C	D
2024	I	J	K
2024	X	Y	Z
2025	C	D	D
2025	J	K	K
2025	Y	Z	Z

Thank you in advance!

Answer 1

The formula uses the REDUCE function in cell L23 and drag down.

=REDUCE(H23,H$23:H$31,LAMBDA(a,v,IF(ISERROR(VLOOKUP(a,H$23:I$31,2,FALSE)),a,VLOOKUP(a,H$23:I$31,2,FALSE))))