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.