Excel function for comparing columns with repeated values

I'm using excel and have two columns (A & B) with values

I want to search for each value in Column A and return the position in Column B. I'm using this formula:

IFERROR(MATCH("Values in Column A";"Array = Column B";0);0)

The results are:

<!-- language: lang-none -->

Column A  | Column B  | Column C
  1       |   4       |   2
  2       |   1       |   3
  3       |   2       |   4
  4       |   1       |   1
  1       |   2       |   2
          |   3       | 

It works fine if it doesn't encounter repeated values. However, I want it to encounter repeated values, so the formula should ignore the ones it was encountered before and go through the others. So the correct result should look like this:

<!-- language: lang-none --> <pre><code>Column C 2 3 5 1 <b>4</b></code></pre>

Can you help me on this? Is there a VBA routine for this?

From the article Getting the 2nd matching value from a list using VLOOKUP formula, you can create a helper column to affix the instance number of each value, to create unique id's.

For example, in Column C, add the following function:

=A1&"-"&COUNTIF($A$1:A1,A1)

Note: The relative reference on the count range will cause the applicable range to grow as it is dragged down. The count of the items matching that cell in a range containing only that cell should always be one. As it gets dragged down to include other cells, it will increment accordingly.

Then add the same thing in Column D to get the instances of cells in Column B:

=B1&"-"&COUNTIF($B$1:B1,B1)

Finally, do the math you want to do in Column E like this:

=IFERROR(MATCH(C1,D:D,0),0)