Hi, I'm new here, so I hope I've posted this with all relevant information and that I didn't violate any rules (not as far as I'm aware off).

I've got 2 columns, 1 with facility names and the other one is where the formula has to come, but the formula also has to look at this same row looking for a reference.

The first column isn't sorted and because of the way data is entered this can't be done either.

Column1 Column2

A 1

B 1

C 1

B 2

B 3

D 1

D 2

So what the formula in column2 needs to do is the following:

- look in column1 and see if the related field has a value that already has been shown in column1 (in above example row 4 has a 2nd B), if it's the 1st of it's kind put 1 if it's the 2nd or 3rd of it's kind put current max +1 (2 or 3 etc).

I was thinking off combining vlookup with max, but it doesn't work because it takes the first value instead of the last value.

=IF(IFERROR(VLOOKUP($A8;$A$2:$A7;1;FALSE);1)=1;1;(VLOOKUP($A8;$A$2:$B7;9;MAX($B$2:$B7)))+1)

Above formula doesn't work because I'm trying to combine the max function in the range lookup part.

This should be possibleI htink, but I can't get it to work.

Hope some of the excel guru's overhere can help me.