VLOOKUP with never return same value

Question

I need some help with Excel's VLOOKUP function.

I have two worksheets.

The first one contains running numbers with item codes and a column for batch numbers.

It looks like this

A   B        C      D        E          F
0   0   05-40112R1  0   2966115100050   1
0   0   05-40112R1  1   2966115100050   1
0   0   05-40112R1  2   2966115100050   1
0   0   05-40112R1  3   2966115100050   1
0   0   05-40112R1  4   2966115100050   1

The second sheet contains the batch number with the item codes.

It looks like this

     A           B
05-40112R1  2966115100050
05-40112R1  2966115100051
05-40112R1  2966115100052
05-40112R1  2966115100053
05-40112R1  2966115100054

When I use VLOOKUP it gives me the same value for column E on the first sheet.

=VLOOKUP(C1,'SHEET 2'!A:B,2,FALSE)

How can I get it to only use the value once and then go on to the next value.

So the first table looks like this:

A   B        C      D        E          F
0   0   05-40112R1  0   2966115100050   1
0   0   05-40112R1  1   2966115100051   1
0   0   05-40112R1  2   2966115100052   1
0   0   05-40112R1  3   2966115100053   1
0   0   05-40112R1  4   2966115100054   1

I have a lot of data like this and copying and pasting is just take too long.

Answer 1

In Sheet1!E1 as a standard formula,

=INDEX(Sheet2!B:B, AGGREGATE(15, 6, ROW(Sheet2!B$1:INDEX(Sheet2!B:B, MATCH(1E+99, Sheet2!B:B)))/(Sheet2!A:A=C1), COUNTIF(C$1:C1, C1)))

That assumes that Sheet2!B:B are real numbers.