Excel Custom Format for Brand Codes: Why Seven Conditions Break

The format [=1]Chinese Gold [=2]Zhou Dasheng [=3]Golden Family; [=4]Bai Tai jewelry; [=5]Chinese bride; [=6]Mengxiang Silver Jewelry; [=7]Shiwauo; is trying to make an Excel custom number format behave like a seven-row lookup table. Excel may reject this format string because it does not follow custom-format syntax. Keep the numeric code in the cell and return the brand name with a lookup formula.

Why the original custom format is not a seven-brand dictionary

The Microsoft custom number format guidelines say a custom number format can have up to four semicolon-separated sections: positive, negative, zero, and text. That is different from a lookup table. The sections decide how categories of values are displayed; they are not seven independent rows of code → brand data.

Conditions are also not the same as lookup rows. Microsoft documents conditions in square brackets and gives a two-section example such as values less than or equal to 100 and values greater than 100. In the usual custom-format pattern, the first two numeric sections can carry conditions, while the third numeric section is the fallback for values meeting neither condition, and a fourth section can format text. When conditions are used, the sections no longer simply mean positive, negative and zero. For seven exact brand codes, a lookup table is the maintainable solution.

Your original string has three separate problems:

• Text is not quoted. Microsoft's custom-format guidelines say text in a number format should be in double quotation marks.
• Semicolons split sections. Semicolons do not create unlimited conditions; they split the positive, negative, zero, and text sections.
• Conditions are limited display logic. They can choose display formats for numeric cases, but they are not a scalable mapping table for codes 1 through 7.

A valid custom format for only two codes

If you only needed to display code 1 and code 2 specially, a quoted custom format can be used as a small display trick:

[=1]"Chinese Gold";[=2]"Zhou Dasheng";General

That example leaves other numeric values in General. It is useful for showing why quotation marks and semicolon sections matter, but it is still not the right design for seven brands. As soon as the list grows, move the data into cells.

Use a lookup table for codes 1 to 7

The Microsoft number format code guidelines explain that number formats change appearance without changing the actual cell value. That is good when the cell should remain numeric, but here you need a separate brand result. Put the code in A2, then create a real lookup table:

Put the headings in D1:E1 and the seven data rows in D2:E8. Enter the numeric code in A2 and this formula in B2, not in A2 or the custom-format dialog:

=XLOOKUP(A2,$D$2:$D$8,$E$2:$E$8,"Unknown code",0)

The Microsoft XLOOKUP function reference documents lookup_array, return_array, if_not_found, and exact match mode 0. The same reference notes XLOOKUP is not available in Excel 2016 and Excel 2019, so check your actual Excel version against the official function reference.

In locales where Excel formulas use semicolons as argument separators, use:

=XLOOKUP(A2;$D$2:$D$8;$E$2:$E$8;"Unknown code";0)

Older Excel: use exact-match VLOOKUP

If XLOOKUP is not available, use VLOOKUP with exact match:

=IFERROR(VLOOKUP(A2,$D$2:$E$8,2,FALSE),"Unknown code")

The Microsoft VLOOKUP function reference explains that VLOOKUP uses FALSE or 0 for exact match, while TRUE or an omitted fourth argument is approximate match. Approximate match is dangerous for brand codes: even if the lookup table is sorted, an unknown code such as 8 can return the closest previous code, such as 7, instead of telling you the code is invalid.

Semicolon-locale version:

=IFERROR(VLOOKUP(A2;$D$2:$E$8;2;FALSE);"Unknown code")

Lock the lookup range when copying formulas

The $D$2:$E$8 part is an absolute reference. The Microsoft guide to absolute cell references explains that relative references change when copied, while absolute references with dollar signs keep the original cell reference. If you fill the formula down many rows, the lookup table should stay fixed.

Handle blanks and unknown codes deliberately

• Use "Unknown code" for 0, 8, blank-as-zero problems, or mistyped codes if they should be visible.
• If blank input should stay blank, use =IF(A2="","",XLOOKUP(A2,$D$2:$D$8,$E$2:$E$8,"Unknown code",0)).
• Keep codes numeric in both A2 and column D. If a pasted value looks like 1 but is stored as text, convert it consistently before troubleshooting the lookup. Keep one row per code; a duplicated code can return the first matching brand.
• Keep brand names in the lookup table, not buried in a long custom format, so spelling changes require one edit.