SEARCH function for multiple variables - VLOOKUP?

"Also my countries are all " South Korea"(space before the first letter). How do I remove an initial space? And not all the spaces? Normally I would do a find and replace!"

That first "space" appears to be some sort of other spacing character, perhaps as the result of you importing the file from some other software? If you go into the word, highlight that portion (this spacing character) of the string only, copy, then exit.

Now do a Find & Replace by pasting this character into the Find what: field and leaving the Replace with: field blanks. That should remove all these but leave "normal" space characters untouched.

Re First Names, please test this array formula** in G2:

=IFERROR(INDEX(Sheet2!$D$3:$D$1221,MATCH(MAX(IF(ISNUMBER(SEARCH(Sheet2!$D$3:$D$1221,B2)),LEN(Sheet2!$D$3:$D$1221))),IF(ISNUMBER(SEARCH(Sheet2!$D$3:$D$1221,B2)),LEN(Sheet2!$D$3:$D$1221)),0)),"")

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).