Boxes printed before the catalog migration still carry old item codes. Look up today’s catalog first, then check the retired list only when the first result is missing.
Set the priority with one example
Put the current code and description in E2:F3 and the legacy pair in H2:I3. In the current list, X-07 means New sleeve and N-14 means Current insert. In the legacy list, X-07 means Old sleeve and L-14 means Legacy insert.

Illustrated catalogs. Decide which list wins when a code appears in both.
Put the code to look up in A2. Use exact matching in both tables.
=IFNA(VLOOKUP(A2,$E$2:$F$3,2,FALSE),VLOOKUP(A2,$H$2:$I$3,2,FALSE))Check each route before filling down
X-07 should return New sleeve, L-14 should return Legacy insert, and Q-99 should return #N/A. Check for duplicate codes within each list before using the results in an order. IFNA handles #N/A. A broken reference or another unexpected error remains visible for repair.

Illustrated check. Test a shared code, a legacy-only code and a miss.



