Function Problems

Look Up Values in Excel Cells With Custom Number Formats

Elise WilliamsElise Williams Nov 18, 2026 871 views

Question details

The user needs to look up values in Excel cells that display units through custom number formats.

Look Up Values in Excel Cells With Custom Number Formats
Product
Microsoft Excel
Device & OS
not provided
Scenario
A lookup compares values in cells that display units such as kg, g, or mg.
Observed behavior
Excel lookup functions compare the stored values rather than text added by custom number formats.
Before you start

Select a source cell and check the formula bar to see whether its unit is stored as text or only displayed by formatting. If units vary, determine whether they are recorded separately or only indicated by each cell's format.

Solution 1Recommended

Look up the underlying numeric value

Use this approach when the lookup should match the stored number and the unit is only for display.

1
Enter a number

In the lookup cell, such as I5, enter 5000 rather than the text 5000 kg.

2
Apply a display format

Select I5, open Format Cells, choose Number > Custom, and enter 0" kg" to display the value as 5000 kg.

3
Match the stored value

Enter =MATCH(I5,A2:A10,0) in a result cell to find the first exact numeric match in A2:A10.

Look up the underlying numeric value
Formatting does not distinguish units: A stored value of 500 formatted as kg, g, or mg is still the same number to MATCH. Record units separately and convert values to a common unit when the units affect the result; changing the format alone does not convert a value.
Work with spreadsheets in WPS Office

Look up formatted numbers in WPS Spreadsheet

Use WPS Spreadsheet to enter a numeric lookup value, display a unit with a custom format, and find the stored number with MATCH.

  1. 1. Open the workbook: Open WPS Spreadsheet and select Open to choose your Excel workbook.
  2. 2. Enter the lookup number: Select I5 and enter 5000 as a number, without the unit.
  3. 3. Format the display: Select I5, open Format Cells, choose Custom, and enter 0" kg" to display the unit.
  4. 4. Find the value: In a result cell, enter =MATCH(I5,A2:A10,0) to find the first exact match in the source range.
Match underlying numbers without typing displayed units into the lookup valueOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface to work with formulas and cell formatsWork with spreadsheets using the free version of WPS Office
QA img-9

Frequently Asked Questions

Why doesn't MATCH find 5000 kg in a formatted numeric cell?

If kg is added by a custom number format, the cell stores the number 5000, not the text 5000 kg. Enter 5000 in the lookup cell and use an exact-match formula such as =MATCH(I5,A2:A10,0).

Can Excel distinguish 500 kg from 500 g by their cell formats?

No. If both cells store 500, MATCH sees the same numeric value. Store the units separately and convert the quantities to a common unit, or store the combined values and units as text.

Does a custom number format convert kilograms to grams?

No. A format changes the display, not the stored value. Use a formula that performs the conversion and record the unit separately when needed.

What does MATCH return if the same value appears more than once?

With 0 as the third argument, MATCH returns the position of the first exact match in the specified range. It does not distinguish cells by their displayed number formats.