Vlookup why n/a

The most obvious indications of numbers being formatted as text are shown in the screenshot below. The numbers can also be stored in the General format. In this case, there's only one noticeable sign - numbers get aligned to the left side of a cell, while numbers stored as numbers are aligned to the right by default.

Solution: If this is just a single number, simply click on the error icon and choose " Convert To Number " from the context menu. If you are pulling data from another workbook, you have to include the full path to that file. More precisely, you have to enclose the workbook's name including the extension in square brackets [], and then specify the sheet's name followed by the exclamation mark. Also, you should have apostrophes around all this in case either a workbook or spreadsheet name contains spaces.

The above formula will search for the value of cell A2 in column B of Sheet1 in the " New Prices " workbook, and return a matching value from column D. It's hard to imagine a situation when someone would want to enter a number less than "1" to specify the column to return values from.

Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback! You can always ask an expert in the Excel Tech Community or get support in the Answers community. Convert numbers stored as text to numbers. MATCH function. Overview of formulas in Excel. How to avoid broken formulas. Detect errors in formulas. Keyboard shortcuts in Excel.

All Excel functions alphabetical. All Excel functions by category. Formulas and functions. Using the Approximate Match vs. A formula using a predefined or user-defined function is missing one or more required arguments. This can be done manually, but if you want to change several cells, it may be faster to use the Excel Trim function, as follows:. Note that the Trim function removes trailing spaces from the start and end of a text string. However, if you have other unseen characters, you may need to carry out the above steps using the Excel Clean function instead of or as well as the Trim function.

Further Vlookup troubleshooting tips are provided, in the form of a handy Vlookup Quick Reference Card, on the Microsoft Office website.