Hello, Excellers welcome back for another Excel blog post in my 2020 series where I share my #FormulaFriday #Excel tips. Today let’s look at a really handy Excel formula or a number of formulas that will tell us what numbers are missing in a sequenced data set. Cool huh?. Let’s get writing that formula to find those missing values in Excel.
We are going to use a number of Excel formulas. We will begin with the logical IF Function. Here is my sample data set. I can clearly see that some of the numbers from 1 to 20 missing. But just which ones?.
Find The Missing Values In Excel.
Starting With The IF Function.
We start our formula with the IF Function which will test if a condition is met returning one value for TRUE and another for FALSE. We will need to test if we find an error (#N/A) or not. Which is why we first then use the ISNA function which checks if a value is #N/A and returns TRUE or FALSE. So here is the first part of our formula set up.
Bring In The VLOOKUP Function.
Next, we take the VLOOKUP Function to lookup the ROW number that relates to cell A1 (which returns the value 1) in the list of values that are in cells A1 to A20. We are looking to see if the VLOOKUP returns #N/A.
Finishing The IF Function.
We now specify what we need to display in the cell if the row number is not found. This is the ‘value if true’ part of the IF Function. We specify that we find #N/A then Excel must return the row number.
If the result of the VLOOKUP Function is not #N/A and the row number is found then we simply get Excel to return a blank cell using the double quotes ” “.
Dragging The Excel Formula.
So, my final stage of the formula is to simply drag the formula to row 20 to allow Excel to see what rows up to and including 20 are missing. Sweet. How cool is that?. See the results below. Excel has identified exactly which of the values are missing. Have you used this method before or another way?. Share in the comment below. Would you use MATCH Function or how about some Excel VBA code?.
That’s it. A cool way to fill missing values in Excel.
What Next? Want More Excel Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
A Full list Of Formula Friday Blog Posts
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.
