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.

The current catalog lists X-07 as New sleeve while the legacy catalog lists X-07 as Old sleeve.

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.

X-07 uses the current entry, L-14 uses the legacy entry and Q-99 returns #N/A.

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

Microsoft’s Excel function reference