How to Compare Two Columns and Add Missing Values in Excel

There will be instances when you need to compare two columns in Excel and fill in missing values from one column to the other. This might seem daunting at first, but with the right technique, it can be quite simple. Let's walk through the steps.

Using Excel's VLOOKUP Function

1. Assume you have data in columns A and B, and you want to fill missing values in column B from column A.
2. In the first cell of column C (C1), type the formula =IF(ISNA(VLOOKUP(B1,A:A,1, FALSE)),B1,VLOOKUP(B1,A:A,1, FALSE)) and press 'Enter'.

3. Drag the fill handle down to the end of your data. The missing values from column B are now filled in column C.

4. VLOOKUP function

Using Excel's IF Function and MATCH Function

1. You can also use the IF and MATCH functions together. In cell C1, type the formula =IF(ISNUMBER(MATCH(B1,A:A,0)),B1,"") and press 'Enter'.

2. Drag the fill handle down to the end of your data.

3. IF function

4. MATCH function

Note: After using either of these methods, you may want to copy the new column and use 'Paste Special > Values' to replace the original column B if you no longer need it.

With Excel, you can compare two columns and fill in missing values with ease. Whether you choose to use the VLOOKUP function or a combination of the IF and MATCH functions, this tutorial offers step-by-step guidance.