How to Fix VLOOKUP Not Working in Excel and Google Sheets [8 Fixes]

Part 1. Why Is VLOOKUP Not Woking in Excel and Google Sheets [Common Issues & Reasons]

Wondering why is my VLOOKUP not working between sheets? VLOOKUP is a built-in Excel function used to search for a value in the first column of a range. It is used to return a matching value from another column in the same row. However, this may stop working unexpectedly, and here are the common issues and reasons why:

➤ Common Issues:

• VLOOKUP showing the formula instead of a result or value
• Not working in Excel with text or for numbers
• No result when dragging formulas down
• Show #N/A despite exact-matching values
• Lookup failing between sheets
• Text or number mismatches (e.g., text "123" vs number 123)
• VLOOKUP showing 0 instead of blank

➤ Common Reasons:

• Lookup value not in the first column of the selected range
• Hidden leading or trailing spaces or non‑printable characters
• Mismatched data types (text vs number)
• Default approximation lookup (TRUE) rather than exact match
• The wrong column index was given
• Excel cell formatted as Text instead of General
• Cross‑sheet references with range errors

If you're facing the error Excel VLOOKUP not working despite an apparent match, even value is there, these underlying reasons likely explain why.

Part 2. How to Fix VLOOKUP Not Working in Excel/Google Sheets

VLOOKUP is a powerful Excel function, but sometimes it just doesn't return the expected result. Whether you’re facing the dreaded #N/A error, the formula is showing instead of the result, or you just can’t figure out why it's not working, don’t worry. Below are 7 proven fixes to resolve common VLOOKUP issues in Excel and Google Sheets on different scenarios.

• 1. Ensure the LOOKUP Value Is In the First Column of the Range
• 2. Turn Off the Show Formulas Button
• 3. Convert Text to Number Using the Text to Column Feature
• 4. Remove Spaces/Hidden Characters Using TRIM and CLEAN Function
• 5. Use FALSE to Force an Exact Match (Instead of TRUE)
• 6. Set Cell Format to General Not Text
• 7. Use MATCH to Dynamically Verify Column Index

Fix 1. Ensure the LOOKUP Value Is In the First Column of the Range

VLOOKUP only searches from left to right, so the value you're looking for must be in the first column of your table range. If it’s not, you’ll get an #N/A error or incorrect results.

• In formula =VLOOKUP(A2, A2:C11, 2, FALSE), ensure A2's value to be in A2:A11.

• If not, reorder columns and retry to check if VLOOKUP isn’t working still.

  

Fix 2. Turn Off the Show Formulas Button

Is VLOOKUP showing formula not result? You need to turn off the show formula button to get it working.

• When Excel showing VLOOKUP formula instead of value, go to the top “Formula” Tab in Excel.

• Click “Show Formulas” to disable it.

  

Fix 3. Convert Text to Number Using the Text to Column Feature

VLOOKUP may fail if the lookup value or table data is stored as text instead of numbers, causing mismatches. In this case, you will face the error of Excel VLOOKUP not working with text, and here is how to fix it:

• When VLOOKUP not working for numbers, select the lookup column.

• Go to the top “Data” tab, then “Text to Columns”.

• In the pop-up dialog, click “Finish” to force Excel to reformat text as numbers.

  

• Now, re-enter the formula and try.

Tips:

You can also select the lookup value and click the ⚠️ icon when it shows. Then choose “Convert to Number” from the drop-down menu.

Fix 4. Remove Spaces/Hidden Characters Using TRIM and CLEAN Function

Extra spaces or non-printable characters can break matches. Here is how to fix VLOOKUP NA error:

• If the lookup column is in A2. Then in a helper column, use =TRIM(CLEAN(A 2)) to remove invisible character.

  

• Copy and paste as Values over the original column.

  People Also Read:

  How to Fix Microsoft Excel Cannot Paste the Data Error

• Reapply VLOOKUP and check if you still get VLOOKUP show NA when a value exists.

Fix 5. Use FALSE to Force an Exact Match (Instead of TRUE)

Many users forget to set the final VLOOKUP argument to FALSE. Leaving it out defaults to an approximate match, which only works with sorted data. Exact match lookup avoids errors from sorted or approximate searching.

• Use FALSE to force exact match: =VLOOKUP(A2, B2:C10, 2, FALSE) to require exact matches.

  

• Aftet that, drag down to copy if the Excel VLOOKUP drag down is not working.

Fix 6. Set Cell Format to General Not Text

Excel vlookup shows formula instead of the result? You must ensure that your cell format is not text and general instead.

• Select lookup and table columns when VLOOKUP showing formula, not the result.

• Go to “Home”, then “Number Format”, and choose “General”.

  

• Re-enter the lookup value or formula.

People Also Read:

How to Fix Excel Selecting the Wrong Cell [Quick Guide]

Fix 7. Use MATCH to Dynamically Verify Column Index

Wondering what to do if VLOOKUP is not working in Excel after doing all the methods above? There is still one thing that you can try if you are getting "#REF!" results. Explained in the steps below:

• Use =VLOOKUP(A2, B2:C10, MATCH("ColumnName", B1:C1, 0), FALSE) to ensures the correct column is selected.

• MATCH finds the correct column dynamically when the VLOOKUP function is not working in Excel.

  

Using these solutions can fix your problem in most cases. However, they will not work if your Excel file is corrupted. In that case, the issue does not lie with the contents or formula in the file, but the file itself is not working properly.

To resolve that issue, we will discuss everything you need to do in the next section.

Part 3. Repair Corrupted Excel Files If VLOOKUP Not Working Still

Once all corrections have been made and your VLOOKUP still does not work, chances are your file is corrupted. Errors such as VLOOKUP's giving a #NA in every row, failure to save formulas, or returning random blank cells may be indicators of damage in the workbook structure.

In that scenario, you can attempt a specialized program, 4DDiG File Repair, by downloading the utility to attempt repairing damaged Excel worksheets (.xlsx, .xls, .xlsm, etc.) and get them back in a usable state.

Key Features:

• Supports Excel files with versions 2010 up to the latest ones, as well as older versions.
• Deal with mu;tiple Excel issues: unreadable sheets, showing gibberish, error messages, Excel cannot be opened, etc.
• Repairs corrupted Excel files and other documents (Word, PPT, PDF, PSD, archives, and more)
• Recovers broken formulas, pivot tables, charts, formatting, table data, and more

Steps to Repair Excel File with 4DDiG

• Launch the 4DDiG File Repair tool, and choose "File Repair" in the "Corrupted File Repair" option.

  

• Then click "Add Files" or utilize the drag-and-drop action to add your Excel file where VLOOKUP doesn’t work for repair.

  

• Click “Repair All” to start your one-click file repair solution when you face VLOOKUP not working in Excel.

  

• After repairing files, click the "Save" button or check results before saving with the "Preview" button.

  

People Also Ask about the VLOOKUP No Longer Working Error

Q1: Does VLOOKUP work with a drop-down list?

Yes. VLOOKUP is also able to output based on dropdown choices, provided that the value is identical to the value in a matching cell in the look-up column.

Q2: Can VLOOKUP work across sheets?

Yes, VLOOKUP can use cross-sheet searches. Simply key in the sheet name in your range as follows: =VLOOKUP(A2, Sheet2!A1:B100, 2, FALSE). If that doesn't work, check named ranges and data formatting.

Q3: Does VLOOKUP work on Google Sheets?

Yes. VLOOKUP is supported on Google Sheets, as it is in Excel, with identical syntax. However, make sure that formatting is in keeping and verify that sheets have consistent formatting bounds.

Q4: How to test if VLOOKUP fails?

Check on precision using helper columns. To be sure that your data is in existence and that it belongs to the same data type, use Try =A2=B2 or =ISNUMBER(MATCH(A2, B:B, 0))

Q5: How to fix the VLOOKUP NA error?

Use the TRIM and CLEAN functions to remove spaces, convert text to numbers using Text to Columns, and ensure you're using FALSE for an exact match.

Conclusion

When you are asking yourself why your VLOOKUP not working in Excel /Google S heet, the problem might be as simple as wrong formatting and as complicated as corrupted files. We have discussed all the typical causes of all of these in Excel 2010, 2016, 365, and Google Sheets. These include lack of matches, invalid lookups, and errors in formatting that are hidden.

In any unresolved situations when formulas do not work even though they seem to be fine, apply 4DDiG File Repair to repair potential spreadsheet corruption and revive VLOOKUP functionality.