Need a little help with a formula.

I have this:

LOOKUP(MAX(HCS Leads[HCS Number]), HCS Leads, HCS Number, HCS Number)+1 (One randomly chosen value from this list ( …The list of values of column ‘HCS Number’ …from rows of table ‘HCS Leads’ …where this condition is true: ((The value of column ‘HCS Number’) is equal to (MAX( …HCS LEADS[HCS Number])))) + 1 Its supposed to look up the max number in a column and then write the next highest number to the record.

But it’s not quite doing it. Maybe because the numbers are out of sequence in the column? Maybe because I’m missing something.

Can anyone help me with this expression?

Again it supposed to look up the highest number in the column. Then assign the next number to the record its writing in that column .