Search code examples
excelexcel-formulaoffice-2016

Add non-existent numbers with a zero value in Excel

I have lot of sequential data in Excel.

The missing number in the sequence for Column A, should be populated with zero value in column B. What formula i could use for this?

Data in excel sheet as below:

 A  | B
 0  | 4
 1  | 6
 4  | 4
 6  | 7

Expected output:

 A  | B
 0  | 4
 1  | 6
 2  | 0
 3  | 0
 4  | 4
 5  | 0
 6  | 7
Solution

  • In B1 of your second table

    =IFERROR(VLOOKUP(A1,original_table,2,0),0)

    and drag down. Replace original_table with a reference to your first table