Look Up Values in Excel Cells With Custom Number Formats
Question details
The user needs to look up values in Excel cells that display units through 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.
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.
Look up the underlying numeric value
Use this approach when the lookup should match the stored number and the unit is only for display.
In the lookup cell, such as I5, enter 5000 rather than the text 5000 kg.
Select I5, open Format Cells, choose Number > Custom, and enter 0" kg" to display the value as 5000 kg.
Enter =MATCH(I5,A2:A10,0) in a result cell to find the first exact numeric match in A2:A10.

Store values and units as text for an exact text match
Use this approach when the unit must be part of the lookup value and the combined text does not need to be used directly in calculations.
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. Open the workbook: Open WPS Spreadsheet and select Open to choose your Excel workbook.
- 2. Enter the lookup number: Select I5 and enter 5000 as a number, without the unit.
- 3. Format the display: Select I5, open Format Cells, choose Custom, and enter 0" kg" to display the unit.
- 4. Find the value: In a result cell, enter =MATCH(I5,A2:A10,0) to find the first exact match in the source range.

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.




