Contextures

Contextures News 2021-05-04

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.

Fix MATCH Errors

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

  • there are real numbers in the lookup table (blue cells)
  • you try to match them with text numbers (yellow cells)

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

  • =MATCH(A9,$B$4:$B$7,0)

Excel MATCH function error text vs number

Change the MATCH Formula

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:

  • =MATCH(--A10,$B$4:$B$7,0)

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.

match formula with 2 minus signs

This video shows how to fix Text vs Number problems, and there are written steps on the INDEX and MATCH page.

Excel Articles

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)

Excel Plans: On the Forward Thinking CFO podcast, Brian Jones (Head of Product, Excel) discusses Microsoft's strategy for Excel. There are 2 episodes -- Part 1 and Part 2 (Level - All)

Also see: Previous Newsletter Issues

Fridge Bins

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!

weekly photo

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.

Debra Dalgleish
dsd @contextures.com
ctxdebra @gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Last updated: May 4, 2021 11:51 AM