vlookup if problem

[redJohn89]

Hi,

I have two sheets, sheet 1 and 2. Col A of sheet 1 has the user input which has some typos in the country names. Column B is where i need my typo corrected output. I have another sheet 2 which has the mapping for corrections (column A is the assumed typos and Col B is the mapped good name).

If i encounter a misspelled country name in Col A sheet 1, then in Col B, i need a formula to lookup its corrected name from sheet 2. If the name in Col A sheet 1 is correct(ie no typos), then it should just carry over that name to col B without running the v lookup formula.

[protonLeah]

That's the purpose of Data Validation dropddown lists: save time, prevent entry errors...

[redJohn89]

Hi,

Thanks for your reply...but i need a formula to make it work in this case. Would the if(vlookup) formula work? I am probably making a mistake in how i typed the formula.

[protonLeah]

You need a list of ALL mistakes and variations possible so you can look up each one....

[redJohn89]

It will not be a huge list. Just a sample set. Assuming this is all the typos there is, would you be able to suggest a formula?

[redJohn89]

Nevermind...i was able to figure out the formula. Thanks for your suggestion and time.

[bluesky63]

Hi redJohn89

the quick formula will be
B2
=IFERROR(INDEX(Sheet2!A:A,MATCH(A2,Sheet2!A:A,0)), INDEX(Sheet2!A:A,MATCH(A2,Sheet2!B:B,0)))

[wk9128]

Not sure what you want, provide two different methods

B2 cell formula

[bluesky63]

or

=IFERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE), VLOOKUP(A2,Sheet2!B:B,1,FALSE))