Excel - match data from one range to another and return either another value from a third range OR the original data

1
votes

I'm trying to work with impure data to create a clean template. Right now, I receive an excel document that has a lot of errors in it, but they're always the same errors. Rather than manually going in and fixing it every time, I'd like to set up a formula to automatically fix the errors.

For example, I have a list of values in Sheet 1, column A that always contains the same errors. I have a validation sheet (Sheet2) that contains all the error values in column A and the correct values in column B. I'd like to set up a third tab (Sheet3) that contains a formula that compares the values from Sheet1 column A against the values in Sheet2, and if present displays the corrected value from column B, otherwise it displays the original (correct) value from Sheet1 column A.

I think the formula would be similar to the one I found here which returns the corrected values for the errors, I just need that last step that returns the original value instead of #NA if there isn't an error. Something like this:

=VLOOKUP('Sheet1'!A2,'Sheet2'!A:B,2,'Sheet1'!A2)

Any help is appreciated.

1
excelformulavlookup
The accepted answer on the question you mentioned shows you how to use IFERROR() to catch the #N/A. Instead of the "" blank string, you want the original value instead, which is your value from Sheet1. – Orbling
Something like (in Sheet3!A2) : =IFERROR(INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A2,Sheet2!$A:$A,0)), Sheet1!$A2) – Orbling

1 Answers

0
votes

For the sake of an answer and same principle as @Orbling. Assumes all columns are labelled: 

=IFERROR(VLOOKUP(Sheet1!A2,Sheet2!A:B,2,0),Sheet1!A2)

in Sheet3 A2 and copied down to suit.