How to Replace Excel Data from Another Sheet Using VLOOKUP
Question details
The user needs to replace existing data with updated values from another worksheet based on matching criteria, while keeping the original data intact if no match is found.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating a dataset with new information (such as primary contact emails) stored in a separate worksheet.
- Observed behavior
- A VLOOKUP function combined with IFERROR allows data replacement when a match is found and preserves the existing cell values otherwise.
Ensure both worksheets are open in the same workbook. Identify the common column (like a name or ID) that exists in both sheets to act as your shared lookup value.
Use IFERROR and VLOOKUP to Replace Data via a Helper Column
Combine VLOOKUP with IFERROR in a temporary helper column to pull updated data or fall back to original data, then paste the results over the original column as values.
This method uses a helper column to calculate the new values. The VLOOKUP function searches for the matching record in the second sheet. If a match is missing, it normally returns an error, but wrapping it in IFERROR tells the application to keep the original value instead.
In an empty column (e.g., Column I) on your main sheet, select the first blank cell (e.g., I2) where you want the updated data to appear temporarily.
Type `=IFERROR(VLOOKUP(J2,Sheet2!$F$2:$G$8,2,0),K2)`. In this example, `J2` is your shared lookup value, `Sheet2!$F$2:$G$8` is the lookup range on the other sheet, `2` is the column index to return, and `K2` is the original data cell to fall back on if no match is found.
Press Enter to calculate the first result. Then, double-click or drag the fill handle at the bottom-right corner of the cell down to apply this formula to the rest of the column.
Select the entire helper column and copy it. Right-click the first cell of your original data column (e.g., Column K) and choose 'Paste Special > Values' to overwrite the old data with the newly matched data. You can then delete the helper column.
Effortlessly Manage and Update Data with WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced functions like VLOOKUP and IFERROR, making complex cross-sheet data replacement seamless, accurate, and completely free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your target and source worksheets.
- 2. Enter the formula: Select an empty helper cell and enter your `=IFERROR(VLOOKUP(...))` formula.
- 3. Fill the column: Drag the fill handle down to apply the formula across your entire dataset.
- 4. Paste as values: Copy the calculated results, right-click the original column, and select 'Paste as Values' to finalize the replacement.
Frequently Asked Questions
Why is my VLOOKUP formula returning a #N/A error despite using IFERROR?
If IFERROR is wrapping the VLOOKUP correctly, you shouldn't see an #N/A error. Ensure the syntax is `=IFERROR(VLOOKUP(...), fallback_cell)`. If the fallback cell itself contains an error, the formula will display that error.
How can I prevent my lookup range from changing when I copy the formula down?
Use absolute cell references for your table array by pressing the F4 key when selecting the range (e.g., changing A1:B10 to $A$1:$B$10). This locks the lookup area in place.
What does the '0' mean at the end of the VLOOKUP formula?
The '0' (or FALSE) tells VLOOKUP to look exclusively for an exact match. If you omit it or use '1' (TRUE), it will look for an approximate match, which is usually incorrect when searching for specific text like email addresses or names.
Can I use XLOOKUP instead of VLOOKUP and IFERROR for this task?
Yes. If your spreadsheet software supports XLOOKUP, it is a great alternative. It has a built-in 'if_not_found' argument, which eliminates the need to wrap the formula in an IFERROR function.
