Compare two columns in EXCEL and if they are equal return a certain value

Question

I’m having a hard time generating a logical structure (If) to validate some data. It’s for a survey. follows the explanation:

• There are two columns (B and H). In the column B there are all Brazilian municipalities and in column H there are not all (but most).

• The column I contains the notes obtained by the municipalities in the column H

• The column C should receive all data from the column I in their respective municipalities
• As in the column B there are more municipalities than in the column H (as previously mentioned) I am trying to create a rule in VBA that compares the column B with the H, if the municipalities compared are equal, the data in the column I will move to the column C in their respective municipality
• Ex: Compares B2 to H2, if equal, C2 gets I2.

• I tried to do in VBA but as I could not get anywhere near the goal, I do not consider it necessary to post the code (which is messy and not commented). If someone knows a way to do it without VBA, no problem, the important thing for me is the result.

• Photo of the expectation of how it should look:

OBS: The municipalities in red in the column B are the ones who are not H

The columns D and And can be disregarded, I understand how to do for one I can already do for them.

Answer 1

In this case you do not need to do in VBA, you can use the PROCV as follows:

In the cell C2:

=PROCV($A2;$H$2:$I$6000;2;FALSO)

In the example above I put as having around 6,000 municipalities, but look at the number of lines you have in your spreadsheet.

To complement could use the following formula to appear the n/a as quoted in your question:

=SE(ÉERROS(PROCV($A2;$H$2:$I$6000;2;FALSO));"n/a";PROCV($A2;$H$2:$I$6000;2;FALSO))

I hope I’ve helped!