Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Combining vlookup and max but then slightly different


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.


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. 



Selected Answer

The formula below will return the result you have shown.


Observe the $ sign in A$2:A2.  As this formula is copied down the second A2 will change to A3, A4 etc. (same as the A2 specifying the criterium). A range like A$2:A2, A$2:A3. A$2:A4 is created, different for each row. Since the range ends on the row of the search criterium that criterium is also counted and the formula can't ever return 0. But if you want to count only duplicates you could change the range to end one row above the row in which it resides. Or you could simply deduct 1 from the result, like =COUNTIF(A$2:A2,A2)-1



I'm so ashamed, right now. 
I was making it way to complex.
The countif function does the job perfectly!

Thanks for the solution and I'm going to sit in a corner and cry.......
Namoh (rep: 2) Sep 22, '20 at 3:30 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login