This site is not affiliated with Microsoft Excel; this is an independent explanation.
VLOOKUP not working in Excel usually means one of three things: the formula shows as plain text instead of calculating, it returns #N/A even though the value looks present, or it returns the wrong row. Each has a clear cause, and none of them needs a new formula from scratch.
The same kind of mismatch also explains most other formula troubles, such as SUM giving 0, a filter missing rows, or the drag handle doing nothing. Take the checks that follow one at a time, beginning with what is visible on screen and finishing with settings. If the arrow keys are the real trouble, go to arrow keys not working in Excel.
Common causes
1Numbers stored as text, or the other way round
How to tellSmall green triangle in the cell; lookup shows #N/A though both values look the same
What fixes itConvert both columns to the same type, using Text to Columns or the warning icon
2Extra spaces or hidden characters
How to tellValues look equal but LEN shows different lengths
What fixes itWrap the lookup value in TRIM, or clean the column first
3Approximate match used by mistake
How to tellWrong row returned, or results change when data order changes
What fixes itAdd FALSE or 0 as the fourth argument for an exact match
4Lookup value is left of the return column
How to tellVLOOKUP cannot find a value that sits to the left of the key
What fixes itReorder columns, or use INDEX with MATCH, or XLOOKUP if you have it
5Calculation set to Manual
How to tellFormula changes do not update until you press F9
What fixes itOn the Formulas tab, set Calculation Options to Automatic
6Cell formatted as Text
How to tellThe formula itself is shown in the cell instead of the result
What fixes itSet the format to General and re-enter the formula with F2 then Enter
7Table range not locked
How to tellWorks in the first row and breaks when copied down
What fixes itLock the table range by pressing F4 while it is selected
How to fix it, step by step
Check the error shown
#N/A means the value was not found. #REF means the column number is bigger than the table. #VALUE means a wrong type of input. #NAME means the function name is mistyped. Knowing which one you have cuts the search a lot.
Make sure calculation is automatic
Open the Formulas tab, click Calculation Options and pick Automatic. If it was on Manual, press F9 once to refresh. This one setting explains many cases of formula is not working.
Show the formulas to see what is real
Press Ctrl + ` (the key above Tab) to toggle formula view. If every cell shows a formula, that view is on. If only one cell shows a formula as text, its format is Text, or it starts with an apostrophe.
Fix cells that show the formula, not the answer
Select the cells, set the number format to General, then press F2 and Enter in each cell. A leading apostrophe or a space before the equals sign also turns a formula into text.
Match the data types in the lookup and table
Left aligned numbers are usually text. Select the column, open Data, then Text to Columns, and click Finish to convert text numbers in one go. Do this for both the lookup value and the key column of the table.
Remove spaces and use an exact match
Use
=VLOOKUP(TRIM(A2),E2:G200,2,FALSE)as a pattern. Press F4 while selecting the table range to lock it. The last argument FALSE asks for an exact match, which is what you want for names, IDs and codes.Count the column index again
The third argument counts columns inside the table you chose, starting at 1 for the first column of that table, not from column A of the sheet. If you inserted a column, the number may need to change.
Try INDEX and MATCH if the key is not first
When the value to find is to the right of the answer, use
=INDEX(C:C,MATCH(A2,B:B,0)). It works in any version and does not break if columns move.
VLOOKUP not working in Excel even when the value is there
The values look the same to you but not to Excel. A product code typed as 1045 in one sheet and imported as "1045" text in another will never match. An invisible space at the end, copied from a website or a PDF, does the same. The quickest test is a spare cell with =A2=E2. If it says FALSE, they differ in type or hidden characters.
You look up roll number 214 and get #N/A. The student list has 214 stored as text, while your sheet has it as a number. Converting either column fixes it without touching the formula.
SUM returns 0 and other number problems
If a SUM gives 0, the numbers are almost certainly text. Look for a green triangle or left aligned digits. Select the cells, click the warning icon and choose Convert to Number. Date problems have the same root: if a date in dd/mm/yyyy form is not recognised, the computer's region format expects month first. Check the date format in Windows region settings and in Format Cells.
Filter, drag and copy paste problems
| Problem | Likely reason | What to try |
|---|---|---|
| Filter skips rows | Blank row or column breaks the data range | Select the whole table first, then apply the filter |
| Fill handle missing | Setting is off | File, Options, Advanced, tick Enable fill handle and cell drag and drop |
| Copy paste does nothing | Sheet protected, or another copy is active | Press Esc, unprotect if you own the file, then try again |
| Paste shows wrong values | Pasting formulas over a filtered range | Use Paste Special, then Values |
| Whole Excel is slow or stuck | Large file, add-ins | Open in safe mode with Ctrl held |
When the whole program misbehaves
If Excel freezes or formulas only fail in one workbook, copy the sheet into a new workbook and test. If it works there, the old file has a damaged part. Use File, Info and check for a repair option, or restore from an earlier version. Keep a copy of the original before any experiments. For a Windows or keyboard issue behind the problem, see Windows error codes and laptop keyboard not working.
Frequently asked questions
Why is VLOOKUP not working in Excel when both values look the same?
One is probably text and the other a number, or one has an extra space. Test with a cell that compares them, then convert the type or use TRIM.
Why does my Excel formula show as text and not calculate?
The cell is formatted as Text or the entry starts with an apostrophe or a space. Change the format to General, then press F2 and Enter.
Why are formulas not updating in Excel?
Calculation is probably set to Manual. Switch it to Automatic under Formulas, Calculation Options, or press F9 to refresh once.
How do I fix the #VALUE error in an Excel formula?
It means a function got a wrong kind of input, for example text in a place that needs a number. Check the cells it refers to and remove spaces or text from them.
Why is the filter not working in Excel for the whole column?
A blank row or a merged cell often splits the data. Select the full table, remove blank rows and apply the filter again.