This post demonstrates how to find the missing piece from a given series in excel.It could be used to validate the data with a predefined series.
Let’s see how to find the missing values in a series in excel.
We will see a formula that we could use to find the missing values in a series whose starting value is known to us.
The series below starts with 20.
Step 1
Enter the formula below in cell C1
=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT(“20:”&MAX($A$1:$A$9))),$A$1:$A$9,0)),ROW(INDIRECT(“20:”&MAX($A$1:$A$9)))),ROWS(D$1:D1)),””)
Notice that I have hard coded the formula with 20 in the Indirect function, which is our starting number in the series. Change 20 to the number your series starts with.
You don’t have to worry about the maximum number at which the series ends because the MAX function will automatically take care of that.
Also it’s an array formula so press Ctrl+Shift+Enter after entering the formula.
Step 2
Copy the formula in cell C1 to the entire column to see the missing values in the series.
As only 23 and 25 are missing from our series, they are displayed.
Hope this helped.
