# Excel VLOOKUP #N/A error how to fix on Mac, step by step for Microsoft 365

> VLOOKUP #N/A means no exact match, or a match Excel cannot see. Fix the formula with FALSE, TRIM, and IFNA on Mac, step by step.

URL: https://www.lazur.app/for/developers/problems/excel-vlookup-na-error-fix-steps
Updated: 2026-11-06

VLOOKUP shows #N/A when it cannot find your lookup value in the first column of the range. Usually the value is there, but Excel does not see it as a match. Check for extra spaces, text numbers, and approximate match, then add FALSE as the last argument.

## Quick answer

- #N/A from VLOOKUP means no match, or a match Excel cannot see.
- Set the last argument to FALSE for an exact match on most lookups.
- Use TRIM and matching data types when two values look the same but are not.
- Wrap the formula in IFNA to hide only #N/A, not every error.
- On Mac, XLOOKUP defaults to exact match, but check your Excel version first.

## What vlookup #n/a errors costs you

- **Time lost each week.** DOSS's 2026 survey of 1,003 U.S. operations professionals reports 3.6 hours a week spent fixing spreadsheet errors. The survey covers spreadsheet errors in general, not #N/A alone. For one worker, that is more than 22 full workdays a year.
- **Wrong numbers in reports.** Manual data entry (59%), formula errors (46%), and copy-paste issues (45%) are the most common error types in that survey. A #N/A you hide with IFERROR can hide a wrong total too.
- **The cost of one significant error.** DOSS gives an average cost of $4,315 for a single significant spreadsheet error. That figure comes from a vendor that sells an alternative to spreadsheet-based operations. Treat it as a rough signal, not a rate for your team.
- **Doubt about the whole file.** A 2024 literature review by Poon et al. reports that 94% of business spreadsheets contain errors. The review does not explain how it reached that figure. Use it as a reason to test your own lookups, not as a measurement of your file.
- **Repeat fixes.** When a colleague copies a broken formula, the error comes back. If you fix only the first row, the rest of the column stays wrong until someone checks it. That repeat work is the cost most people feel first.

## Why it happens

1. **No exact match in the table.** The value truly is not in the lookup range. This happens when a source row is missing, misspelled, or stored on another sheet. Check the source list first. If the value is absent, no formula fix will find it, and IFNA can label the gap.
2. **Text and numbers that look the same.** A product code can be the number 1042 on one sheet and the text 1042 on another. Excel treats them as different values, so the match fails. Text numbers usually sit left in their cells, and the formula bar may show a leading apostrophe.
3. **Spaces you cannot see.** A leading space, a trailing space, or a double space between words changes the value. Excel compares characters exactly, so the match fails. TRIM removes ordinary spaces, but it does not remove non-breaking spaces from web data.
4. **Approximate match left on.** When the last argument is TRUE or missing, Excel uses approximate match. It can return #N/A when the value is smaller than the smallest value in the first column. It can also return a row you did not want. Set the last argument to FALSE for exact matches.
5. **The key column is not first.** VLOOKUP only searches the first column of the range you give it. If the key sits to the right of the value you want, VLOOKUP cannot find it. Move the key column, or switch to XLOOKUP, which can search left.
6. **A #N/A stored in the source.** Some sheets hold #N/A values on purpose, or someone typed or pasted one into the source. A lookup that reads that cell returns the same error. Search the source for #N/A and check whether the error belongs there.

## How to fix it

- **Set FALSE as the last argument.** Type FALSE as the last argument, which is the range_lookup setting. This forces an exact match. The formula then returns #N/A only when the value truly is missing from the first column. Microsoft's fix list includes it, and Google calls it the recommended setting.
- **Trim spaces and match data types.** Use TRIM on text values that carry stray spaces. Use VALUE or TEXT so both sides of the match share a type. Apply the fix to the lookup value or the source column, and test the result on a few rows first.
- **Hide only #N/A with IFNA.** Wrap the VLOOKUP in IFNA and give it the text you want to see, such as a blank or a label. IFNA traps only #N/A. Other errors still show, so a broken range stays visible. IFERROR is broader and can hide problems you need to see.
- **Switch to XLOOKUP when your version supports it.** XLOOKUP uses exact match by default, and its if_not_found argument replaces #N/A. It can also search left. Check Excel > About Excel first, because Microsoft says XLOOKUP is not available in Excel 2016 and Excel 2019.
- **Check the range and the column number.** Make sure the range starts with the key column and runs far enough to include the row you need. The column number must point to the column that holds the answer. A range that ends too early misses rows near the bottom of the list.
- **Fill down only after testing one row.** Fix the first row, check it against the source, then copy the formula down. If you fill down first, you copy the same mistake into every row. Check the last rows too, because a short range can miss them.

Excel shows #N/A in a VLOOKUP cell when the function cannot find a match for the value you gave it. Most of the time the match exists. Excel just does not treat the two values as equal. That gap causes most of these errors, and the fix is usually small.

## What #N/A means in VLOOKUP

Microsoft describes two cases on its troubleshooting page. Either the value does not exist in the lookup range, or it exists but Excel does not see it as a match. The second case frustrates people, because the cell looks correct. You can read both values on screen and still get #N/A.

VLOOKUP has three rules that catch people out. The lookup value must sit in the first column of the range you give it. The column number counts from the left, starting at 1. The last argument sets how Excel matches, and if you leave it out, Excel uses approximate match. Each rule maps to one of the fixes below.

## Why the error costs more than one cell

One #N/A rarely stays in one cell. You copy the formula down a column, and every row inherits the same mistake. A total built on that column can then be wrong in several places, and nobody sees it until someone checks by hand.

The bigger cost is time. In a 2026 survey of 1,003 U.S. operations professionals, respondents said they spend an average of 3.6 hours a week fixing spreadsheet errors. DOSS, which ran the survey, says that adds up to more than 22 full workdays a year per employee.

- **3.6 hours** Weekly time spent fixing spreadsheet errors, average across 1,003 U.S. operations professionals (2026 DOSS survey) ([source](https://www.doss.com/research/spreadsheet-error-costs-operations-thousands))
- **53%** Operations professionals who deal with spreadsheet errors every week (DOSS 2026, vendor-run survey) ([source](https://www.doss.com/research/spreadsheet-error-costs-operations-thousands))
- **$4,315** Average cost of one significant spreadsheet error, per DOSS, a vendor that sells a spreadsheet alternative ([source](https://www.doss.com/research/spreadsheet-error-costs-operations-thousands))
- **94%** Business spreadsheets reported to contain errors in a 2024 literature review, method not explained ([source](https://phys.org/news/2024-08-business-spreadsheets-critical-errors.html))

Read that number carefully. The survey covers spreadsheet errors in general, not VLOOKUP or #N/A in particular. I did not find a study that measures time lost to #N/A alone. DOSS also sells an alternative to spreadsheet-based operations, so its framing may favor its product.

The same survey ranks the most common error types. Manual data entry comes first at 59%, then formula errors at 46%, then copy-paste issues at 45%. A lookup that returns #N/A counts as a formula error, so it belongs in that 46% group.

![Man working at a laptop, checking a spreadsheet error on a Mac in an office](https://lfobmih1tpj3sgpo.public.blob.vercel-storage.com/content-images/problem/excel-vlookup-na-error-fix-steps/excel-vlookup-n-a-error-how-to-1-6da82a.jpg)

## Check the lookup value before you touch the formula

Start with the value you search for. Click the lookup cell and read the formula bar. Look for a leading apostrophe, a space after the last character, or a number that sits left in its cell. Each sign points to a different fix, and the table below shows which.

| If you see | Likely cause | Start with |
| --- | --- | --- |
| Value looks right but is left aligned | Number stored as text | VALUE on the lookup cell |
| Value looks right, with a space after it | Trailing or extra spaces | TRIM on the lookup cell |
| Some rows work, others show #N/A | Approximate match left on | FALSE as the last argument |
| Value is absent from the source list | Missing or misspelled source row | Check the source, then IFNA if a blank is right |

_Match your symptom to a likely cause, then start with the fix in the last column._

## A worked example with a price lookup

Say column A on your report holds product codes. Sheet2 holds a price list, with codes in column A and prices in column B. In cell B2 you type the formula below, then press Return.

=VLOOKUP(A2,Sheet2!$A:$B,2,FALSE)

The 2 tells Excel to return column B of the range. FALSE forces an exact match. If the code in A2 carries a trailing space, this still returns #N/A. Wrap the lookup value in TRIM, like this: =VLOOKUP(TRIM(A2),Sheet2!$A:$B,2,FALSE). TRIM also removes double spaces between words, so it catches more than the ends of the text.

## Fix one: set the last argument to FALSE

The last argument is range_lookup. When you leave it out, Excel uses TRUE, which means approximate match. With approximate match, a value smaller than the smallest value in the first column returns #N/A. A value that is present can also match the wrong row. Type FALSE to force an exact match.

To make the change, click the cell and move to the end of the formula. Replace TRUE with FALSE, or add ,FALSE before the closing parenthesis. Press Return, then copy the fixed formula down the column. Check a few rows against the source sheet before you trust the rest.

## Fix two: match data types and clean the values

Text and numbers look alike, but Excel treats them as different values. Use VALUE to turn text into a number, or TEXT to turn a number into text, so both sides match. Use TRIM for stray spaces. Work in a copy of the sheet, so you keep the original values for comparison.

TRIM has one blind spot. It does not remove non-breaking spaces, which often come from web pages and some exports. Those characters look like normal spaces, but Excel treats them as different. Wrap the value in SUBSTITUTE to replace the character CHAR(160) with nothing, then pass the result to TRIM.

## Fix three: hide only #N/A with IFNA

IFNA replaces only the #N/A error. Microsoft says other error types are not trapped. A broken range or a bad column number still shows, which is the point. Wrap the lookup like this: =IFNA(VLOOKUP(A2,Sheet2!$A:$B,2,FALSE),"Not found").

> **Hiding is not fixing:** IFERROR catches every error, including a broken range, so it can hide real mistakes. Use IFNA when a blank or a label is the right answer. If every row must find a match, find the cause first.

## Fix four: use XLOOKUP on Mac if your version supports it

Microsoft recommends XLOOKUP on its VLOOKUP troubleshooting page, because it returns exact matches by default. You skip the FALSE step. The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]). The if_not_found text replaces #N/A when nothing matches.

=XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$B:$B,"Not found")

XLOOKUP can search left, which VLOOKUP cannot. That helps when the key column sits to the right of the value you want. People who open the file in an older Excel will see a #NAME? error instead, so plan for that before you share it.

### Check your Excel version first

Microsoft's XLOOKUP page lists Mac as supported. The same page also says XLOOKUP is not available in Excel 2016 and Excel 2019, so it contradicts itself on older versions. A February 2024 Mac blog reports that a license for Office 2019 or earlier made the function go missing. To check your version, open Excel > About Excel.

> **Shared files:** If a colleague uses Office 2019, a formula with XLOOKUP will break for them. Keep VLOOKUP with IFNA when the file must work for everyone.

## Worked example: fixing a whole column

1. Copy the lookup value from a #N/A row, then search the source sheet with ⌘ F. If nothing appears, the value is missing from the source.
2. If it appears, add a spare column with LEN on the lookup cell and compare the length with the source value.
3. Fix the formula in the first row. Set FALSE, add TRIM or VALUE, and check the result by hand.
4. Fill the formula down the column only after the first row is right.
5. Scan the column for leftover #N/A values. Wrap with IFNA only where a blank is correct.

## Mistakes people make

- Fixing the first row, then forgetting to fill down the rest of the column.
- Wrapping every formula in IFERROR, which hides broken ranges along with the real misses.
- Sorting the lookup table and expecting approximate match to stay correct. Google warns that unsorted data with TRUE can return wrong results.
- Editing the source sheet without checking the range. If the range ends above the row you need, the match fails.
- Trusting a fix without checking a few rows against the source.

![Analyst reviewing a spreadsheet of data on a computer screen to fix a formula error](https://lfobmih1tpj3sgpo.public.blob.vercel-storage.com/content-images/problem/excel-vlookup-na-error-fix-steps/excel-vlookup-n-a-error-how-to-2-10d071.jpg)

## Google Sheets and Apple Numbers

Google Sheets uses the same logic. Its VLOOKUP takes search_key, range, index, and is_sorted. Google calls FALSE the recommended setting. If you leave is_sorted out, Google defaults to TRUE, and the first column must be sorted ascending or the result can be wrong.

Wrap the formula in IFNA() to replace #N/A there too. Open the sheet in Chrome, click the cell, and edit the formula in the formula bar. The fix looks the same as it does in Excel.

Apple Numbers is less clear. A third-party iWork fan site says Numbers includes XLOOKUP with the same syntax as Excel. That site does not name a Numbers version, and I did not confirm it with an Apple source. Test one lookup in a copy before you depend on it.

## When #N/A is the right answer

Sometimes #N/A tells you something true. The customer is not on the list. The product was never ordered. In those cases, keep the error or show a clear label with IFNA. A labeled blank is easier to explain than a wrong price.

## Where Lazur helps with this

Lazur is a voice assistant for the Mac. Hold ⌥ ⌘ to ask about anything on your screen, and Lazur answers out loud. While you hold the shortcut, it looks at the screen, so it can read the cell and formula you have open. It does not edit the formula for you.

Here is how that fits this fix. Ask what #N/A means in the cell you are on, and Lazur explains it in plain words. Ask where the lookup range is, and Lazur draws a circle and a label around it. Ask how to add FALSE, and Lazur guides you one click at a time, waiting for each click.

Lazur can also draft text. Ask it to draft a note to your team about bad source data, then read the draft before you send it. You can dictate a comment too. Hold fn to dictate text at the cursor in any app.

Be careful with private data. While you hold the shortcut, audio and the screenshot go to AI providers to answer you. They are not stored, and content is never used to train AI models. If your sheet holds client, patient, or payroll data, follow your employer's rules before you share a screen with any AI tool. This matters most for regulated work, such as law, nursing, or accounting.

## What Lazur does not do

- It does not click buttons, change cells, or edit your formulas.
- It is not a plugin or add-in for Excel or any other app.
- It has no partnerships with any app maker.
- It does not replace testing. You still check the result against the source.

## Next steps

Pick the #N/A cell that matters most. Use the table to match its symptom to a cause. Fix the formula in one row, check it by hand, then fill down. Keep a copy of the original sheet, so you can compare results if someone questions a number.

![Office worker at a desk with a computer, working through spreadsheet data](https://lfobmih1tpj3sgpo.public.blob.vercel-storage.com/content-images/problem/excel-vlookup-na-error-fix-steps/excel-vlookup-n-a-error-how-to-3-5712fa.jpg)

## With Lazur

Lazur is a voice assistant for the Mac that lives in the notch. Hold ⌥ ⌘ and ask; it sees your screen, answers out loud, points at buttons and walks you through apps. Hold fn to dictate. Free for 3 days, then $18/month: https://www.lazur.app/download

- “Where is the lookup range in this formula?”: Lazur circles the range and labels it, so you can check that the key column sits first. (Points at it)
- “What does #N/A mean in this cell?”: Lazur explains the error out loud in plain words, using what is on your screen while you hold the shortcut.
- “Where is the lookup range in this formula?”: Lazur draws a circle and a label around the range on screen, so you can check the key column.
- “Walk me through adding FALSE to this formula.”: Lazur guides you one click at a time, waiting for each click. You make each change yourself.
- “What does IFNA do in this formula?”: Lazur explains each argument, so you know what the formula does before you copy it down the column.
- “Draft a note to my team about the bad source data.”: Lazur drafts the message. You read it, edit it, and send it yourself.
- “Hold fn and dictate a comment on the fix.”: Dictation types clean text at the cursor in any app, so the comment lands where you are working.

## A plan you can start this week

- **Day 1: Find the errors.** Scan the sheet for #N/A values. Note how many rows fail. Write down the source sheet name and the lookup column for each formula that returns the error.
- **Day 2: Fix one row.** Test FALSE, TRIM, and VALUE on one row. Check the result by hand against the source sheet. Keep the original sheet as a copy so you can compare later.
- **Day 3: Fill down and recheck.** Copy the fixed formula down the column. Scan again for leftover #N/A values. Wrap with IFNA only where a blank or a label is the correct result.
- **Day 4: Check your version.** Open Excel > About Excel. If you use Office 2019 or 2016, plan on VLOOKUP and IFNA. If you use Microsoft 365, test XLOOKUP in a copy before you rely on it.
- **Day 5: Share with care.** Send the fixed file with a short note on what changed. If the sheet holds client, patient, or payroll data, follow your employer's rules before you share a screenshot with any tool.

## FAQ

### Why does VLOOKUP show #N/A when the value is in the sheet?

Usually Excel sees a difference you cannot see. Check for a trailing space, a number stored as text, or an approximate match. Use TRIM, VALUE, and FALSE to fix each one, then test a few rows by hand.

### Should I use IFERROR or IFNA?

Use IFNA when you want to replace only #N/A. Microsoft says IFNA does not trap other error types. IFERROR replaces every error, so a broken range can look like a missing row.

### What does FALSE do at the end of VLOOKUP?

FALSE forces an exact match. If you leave the argument out, Microsoft says Excel uses TRUE, which means approximate match. That setting can return #N/A or the wrong row.

### Is XLOOKUP better than VLOOKUP on Mac?

XLOOKUP uses exact match by default, can search left, and has an if_not_found argument. Microsoft says it is not available in Excel 2016 and Excel 2019. Open Excel > About Excel before you switch.

### Can Lazur fix my VLOOKUP for me?

No. Lazur explains the error, points at cells, and guides you one click at a time. It does not click buttons or edit the formula. You make the change and test the result.

### Does the same fix work in Google Sheets?

Mostly yes. Use FALSE for an exact match, wrap the formula in IFNA(), and check that is_sorted is set correctly. Google's VLOOKUP help page covers the syntax and the defaults.

### Are the cost numbers about VLOOKUP errors?

No. The survey figures cover spreadsheet errors in general. The 3.6 hours and $4,315 numbers come from a vendor that sells a spreadsheet alternative. No source here measures VLOOKUP errors alone.

## Sources

1. [How to correct a #N/A error, Microsoft Support](https://support.microsoft.com/en-us/excel/how-to-correct-a-n-a-error), Microsoft, checked 2026-11-06
2. [VLOOKUP function, Microsoft Support](https://support.microsoft.com/en-us/excel/functions/vlookup-function), Microsoft, checked 2026-11-06
3. [Quick Reference Card: VLOOKUP troubleshooting tips, Microsoft Support](https://support.microsoft.com/en-us/excel/quick-reference-card-vlookup-troubleshooting-tips), Microsoft, checked 2026-11-06
4. [XLOOKUP function, Microsoft Support](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929), Microsoft, checked 2026-11-06
5. [IFNA function, Microsoft Support](https://support.microsoft.com/en-us/excel/functions/ifna-function), Microsoft, checked 2026-11-06
6. [VLOOKUP, Google Docs Editors Help](https://support.google.com/docs/answer/3093318), Google, checked 2026-11-06
7. [Spreadsheet Error Costs Operations Thousands, DOSS](https://www.doss.com/research/spreadsheet-error-costs-operations-thousands), DOSS, checked 2026-11-06
8. [Study finds 94% of business spreadsheets have critical errors, Phys.org](https://phys.org/news/2024-08-business-spreadsheets-critical-errors.html), Phys.org, checked 2026-11-06
9. [Fixed: XLOOKUP function not available in latest version of MS Excel (macmanus.nl)](https://macmanus.nl/2024/02/14/fixed-xlookup-function-not-available-in-latest-version-of-ms-excel/), macmanus.nl, checked 2026-11-06
10. [Master XLOOKUP in Apple Numbers (iWork Community)](https://iworkcommunity.com/mastering-xlookup-in-apple-numbers-the-future-of-data-lookup/), iWork Community, checked 2026-11-06