VLOOKUP returns a value, not the source cell’s number format. To change how the result looks, format the cell containing the formula; if the lookup fails because one key is stored as text and the other as a number, make their underlying types consistent.
What VLOOKUP number format means
Excel keeps a cell’s stored value separate from its display format. A cell can contain the number 0.25 and display it as 25%, $0.25, or 0.3, depending on its number format. VLOOKUP retrieves the underlying value from the matched row, but it does not copy the matched cell’s formatting, such as its currency symbol, decimal places, date appearance, font, or fill color.
That distinction explains two problems often described as a “VLOOKUP format” issue:
- The lookup succeeds, but the answer looks wrong. Change the formula cell’s number format.
- The lookup returns #N/A even though the keys look alike. Check whether one key is numeric and the other is text. Cell appearance alone cannot confirm that they have the same underlying type.
Changing a cell’s display format usually does not change its value. For example, applying a currency format to 12.5 displays a currency-style amount but does not turn the value into text or change the amount. Conversely, putting a number-looking string such as “00127” in a cell as text preserves its leading zeros as part of the stored identifier.
How VLOOKUP chooses the returned value
VLOOKUP searches for a key in the leftmost column of a table and returns a value from another column in the same row. Its basic arguments are the lookup value, the table range, the return-column number, and the match setting. For most ordinary lookups, use FALSE for an exact match. If the final argument is omitted, VLOOKUP uses an approximate-match mode, which can return an unexpected result when the first column is not sorted as required.
With item IDs in A2:A5, descriptions in B2:B5, prices in C2:C5, and the ID to find in E2, this formula returns the price from the third column of the table:
=VLOOKUP(E2,$A$2:$C$5,3,FALSE)
If E2 contains an ID found in A2:A5, the result is the corresponding value from column C. The dollar signs keep the table range fixed if you copy the formula to other rows. They do not affect the result’s number format. If column C contains 12.5 formatted as currency, VLOOKUP returns 12.5; the result cell may show 12.5, $12.50, or another display depending on its own format.
Format the VLOOKUP result cell
For a successful lookup whose result simply needs a different appearance, format the cell that contains the formula. Select the result cell or range, then use Home > Number Format and choose an option such as Currency, Percentage, Date, or Number. For more control, open the Format Cells dialog with Ctrl+1 on Windows. On a Mac, use Command+1; the dialog and available options can vary slightly by Excel release.
In Format Cells, choose the Number tab and select a category, then set the relevant options. For a price, choose Currency or Accounting and set the number of decimal places. For a percentage, choose Percentage and set decimal places. Remember that Percentage displays a stored value of 0.25 as 25%; it does not calculate a new percentage from a value of 25. If the display should match the source column, apply the appropriate format to the destination cells yourself.
Formatting a result range before entering or copying formulas can also make a report more consistent. It is generally better to keep a numeric result numeric and use cell formatting for display, so that totals, comparisons, charts, and later calculations can continue to use the value as a number.
Check whether the lookup keys are numbers or text
Excel can store the digits 123 as a number or as text. They may look identical in cells, but an exact-match lookup can treat them as different values. A number format does not reliably solve this mismatch: formatting a text value as Number does not convert it to a numeric value, and formatting a number as Text does not necessarily convert an already-entered value into text.
Look for clues such as a green triangle or an apostrophe before an entry, but verify the source rather than relying only on appearance. Compare the lookup key and the table’s key column. Check how the data was imported, whether identifiers contain leading zeros, and whether the values are consistently stored. When the workbook contains many rows, correcting the source column to a consistent type is often safer than adding a conversion to every lookup formula.
A simple test can help distinguish a number from text. With the value being checked in A2, the formula =ISNUMBER(A2) returns TRUE if A2 contains a number and FALSE if it contains text. Use a separate test cell and adjust A2 to the cell you want to inspect. Repeat the check for a lookup key and a corresponding table key, since the two columns may have been imported differently.
Fix a number-versus-text mismatch
First decide what the key represents. Quantities, prices, and measurements are usually numeric. Product codes, account IDs, postal codes, and other labels are often better treated as text, especially if leading zeros matter. Do not convert identifiers to numbers just because they contain digits: converting text “00127” to a number produces 127, which loses information about the original code.
If the lookup value is a numeric-looking text value and the table’s first column contains actual numbers, you can convert the lookup value inside the formula. With the key to find in E2, numeric keys in A2:A5, and return values in B2:B5, use:
=VLOOKUP(VALUE(E2),$A$2:$B$5,2,FALSE)
This works when E2 contains text that Excel can interpret as a number. It is not suitable for identifiers where leading zeros are meaningful, and VALUE returns an error if the text cannot be converted to a number. For a large dataset, consider cleaning the source data consistently instead of relying on an in-formula conversion.
If the table keys are text and the lookup value is numeric, converting the lookup value to text can work when the text keys have the same characters and no significant extra zeros. With a numeric ID in E2, text IDs in A2:A5, and return values in B2:B5, use:
=VLOOKUP(E2&"",$A$2:$B$5,2,FALSE)
The ampersand joins E2 to an empty string, producing a text lookup value. This does not add missing leading zeros. If the table contains “00127” but E2 is the number 127, converting 127 to text gives “127”, not “00127”. Decide on a consistent representation for the IDs and preserve any meaningful zeros in the source data.
Leading zeros, dates, and decimal places
Leading zeros cause confusion because a custom number format can make a numeric value display with zeros that are not part of the stored value. For example, a numeric 127 displayed with the custom format 00000 appears as 00127, but the stored value remains 127. That can be useful for presentation, yet it does not make the value equal to the text string “00127” in every lookup situation. If zeros are part of an identifier, store and match the identifiers consistently as text.
Dates are also numbers in Excel, displayed using date formats. VLOOKUP can return a date serial value, but the result may show as a number if the destination cell is formatted as General. Apply a date format to the result cell to display it as a calendar date. If the lookup key itself is a date, confirm that both sides contain genuine Excel dates rather than one date and one text string that merely looks like a date.
Decimal places are a display choice, too. Showing 3.456 as 3.46 does not necessarily round the stored value; it may only hide digits. That matters if a later calculation uses the result. Use a number format when you only want to change what is displayed. Use a rounding calculation only when you intend to change the value used in subsequent arithmetic.
When to use TEXT in the formula
The TEXT function converts a value into text using a chosen format. It can be useful when a formula must produce a formatted label, but it is usually not the best way to format a normal numeric lookup result. Text output may not behave like a number in arithmetic, sorting, or other formulas, and the format code is embedded in the formula instead of being controlled by the cell’s number format.
With IDs in A2:A5, prices in C2:C5, and the requested ID in E2, this formula returns the matched price as text with two decimal places:
=TEXT(VLOOKUP(E2,$A$2:$C$5,3,FALSE),"0.00")
If the matched price is 12.5, the displayed result is the text “12.50”. Use this approach when creating a text string for a label or export that specifically needs a fixed appearance. For an ordinary price column that needs to remain numeric, keep the VLOOKUP formula alone and apply a two-decimal number format to the result cell instead.
Common VLOOKUP problems that look like formatting issues
A format change cannot fix every incorrect result. If you see #N/A, Excel did not find an exact match for the lookup value. Check text-versus-number types, extra spaces, spelling, and whether the key is actually in the first column of the selected table range. VLOOKUP cannot search to the left of that first column. If the formula returns a value from the wrong row, confirm that the final argument is FALSE and that the return-column number points to the intended column.
If the result is 0 or appears blank, inspect the matched return cell and the formula’s return-column index. A zero may be a genuine stored value; a blank-looking result can also be affected by the destination cell’s format. If the formula itself displays instead of calculating, check that the cell is not formatted as Text and that Show Formulas is not turned on. After changing a Text-formatted formula cell to General, re-enter the formula so Excel evaluates it.
- Unexpected match: use FALSE for an exact match and confirm the table range.
- #N/A for apparently identical keys: compare the stored types and look for spaces or different characters.
- Wrong visual appearance: format the formula cell, not just the source cell.
- Formula shown as text: change the cell format to General and re-enter the formula.
Frequently Asked Questions
Does VLOOKUP copy the source cell’s formatting?
No. It returns the matched value, not the source cell’s number format, font, or fill. Format the destination cell separately.
Why does VLOOKUP return a number instead of a date?
Excel stores dates as numbers and uses number formats to display them as dates. Apply a date format to the result cell, and check that the lookup returned the intended value.
Can I make VLOOKUP return two decimal places?
Usually, format the result cells to show two decimal places. Use TEXT only when you specifically need text output, since the result will no longer be a normal numeric value.
Will formatting a cell as Text fix #N/A?
Not necessarily. A format change does not always convert existing values. Check whether the lookup value and the first-column key are both stored as numbers or both as text, then make their underlying types consistent.
How do I keep leading zeros in a VLOOKUP result?
Store codes with meaningful leading zeros consistently as text, or use an appropriate custom number format when the zeros are only for display. A display format does not change the underlying identifier.
Related
- Excel Unmerge Cells Shortcut: Keys, Steps, and Fixes
- Excel Unmerge Cells and Fill Every Cell with Its Data
- Excel Undo Shortcut: Ctrl+Z and How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- IMAP Not Working in Outlook? Troubleshoot the CauseIf IMAP is not working in Outlook, first confirm your internet connection and your email provider’s current server…
- Excel Workbook Won’t Scroll? Fix Frozen or Stuck Sheets FastIf an Excel workbook will not scroll, first go to View > Freeze Panes > Unfreeze Panes, then…
- Excel Table Heat Maps: Create, Customize, and Fix ThemAn Excel table heat map uses Conditional Formatting to color cells according to their values, making high, low,…
- Why Is Find Not Working in Excel? FixesFind in Excel usually appears not to work because the search is limited to the wrong sheet or…
- Excel Column Is a Number Instead of a Letter? Fix ItIf Excel shows column headings as 1, 2, 3 instead of A, B, C, turn off R1C1 reference…
- Excel Import CSV with Decimal Point: Fix Number ParsingTo import a CSV with decimal points correctly in Excel for Microsoft 365 on Windows, use Data >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.