Excel Fix MATCH Formula Errors
May 4, 2021
Fix errors in MATCH formulas, Microsoft's plans for Excel, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Thank you for reading the news, and you'll get the next email on May 18th.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Your MATCH formula may return an #N/A, even if the value you're looking for is in the lookup range. There's a new video on INDEX and MATCH page on my Contextures site, that shows the problem, and how to troubleshoot and fix the MATCH errors
Sometimes this error occurs because
The MATCH formula in cell B9 results in an error, because A9 has a text number, which doesn't match any of the real numbers
To fix the problem, type two minus signs (double unary) in the formula, before the lookup value. That changes a text number to a real number, so it can find a match.
Here's the formula in cell B10, where A10 is text, and the MATCH function works correctly:
The same formula works in row 11, where there is a real number. The two minus signs don't affect it.
For more MATCH error troubleshooting tips, go to INDEX and MATCH page on my Contextures site.
This video shows how to fix Text vs Number problems, and there are written steps on the INDEX and MATCH page.
Here are a few Excel-related links that you might find useful or interesting.
Functions: Excel functions expert, Norm Harker, reviewed all the Excel function descriptions on the Microsoft site's list, and found many errors. (Level - All)
Office Scripts: In Excel for the web, you can use Office Scripts to automate tasks. Microsoft's Excel team made a Game of Life simulator. Read about the simulator, and get the code to try it yourself, (Level - Int/Adv)
Also see: Previous Newsletter Issues
I just finished reading Real Life Organizing: Clean and Clutter-Free in 15 Minutes a Day, by Cassandra Aarssen. My favourite tip was to set up a household management binder, with emergency contacts, medical info, and other key details. Make your lists in Excel, or go to her Clutterbug website and download free binder sheets. (email & address required)
And did you know this tip? Some fridges have door bins that are easy to remove. Keep your condiments in those, and carry them to the table at meal time. We're ready for Taco Tuesday!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210504ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: May 4, 2021 11:51 AM