Matching 2 columns from different datasets when 1 column is missing values

you can use index/match

=INDEX(D:D,MATCH(A2,C:C,0))

you can just paste the formula say in cell F2 then fill the formula down using fill handle..

I inserted a new column C to hold the values in column D that match the countries in Column A. I also added two columns to cross-check if the other country list contained countries that are not in Column A. Countries missing from Column A are marked in Red. (Conditional Formatting) Countries in column A that are not in the other list of countries have blanks in column C.

You're welcome.

Formula:

=IFERROR(VLOOKUP(A2,$D$2:$E$216,2,0),"")

The IFERROR part returns a blank cell if the VLOOKUP formula doesn't find a value. The VLOOKUP takes the value in A2 and searches down the column D from D2:D216 for a match. When a match is found, the value in column E is returned. The 0 means that the match has to be exact.

Formula:

=IFERROR(VLOOKUP(D2,$A$2:$A$253,1,0), "Not in column A")

This VLOOKUP formula does the same thing as the first formula only this time the formula is looking for a match in the country name. If a match is found, the country name is returned and if not "Not in column A" is returned. This is accomplished by the 1 just after the range $A$2:$A$253.

As a visual cue, Conditional Formatting was used to highlight "Not in column A" by using this formula in Conditional Formatting:

Formula:

=G2="Not in column A"

Select column G and then go to Conditional Formatting, New Rule, Use Rule.... and enter the formula.

Great!! Thanks for the feedback.