Find the highest value from range, multiple entry and adjacent cells date

Hi

In the excel sheet, I have the date in J8:J9999 and Invoice number in the Column K8:K9999.

Invoice number having 4 series as below,
1 to 1000
1001 to 4000
4001 to 5000
10000 to 15000

Invoice number entry will be like this
1&2&3
1005
1006&1007&1008
4004
4005&4006&4007

I used a formula in R4 to find the highest value from 4000 to 5000 is
=AGGREGATE(14,6,K8:K9999/(K8:K9999>=4001)/(K8:K9999<=5000),1)

My expected value in R4 is 4007 (highest value between 4000 to 5000) but it is showing 4004 because it is ignoring to take 4005&4006&4007 entry.

How do I change the formula to get the 4007 in cell R4?

Note: The entry from 4000 to 5000 will be the year of 2019 entries only. I don't know how to get the highest value from this year entry as well as multiple entries too.

Please suggest the formula

Thanks in advance

Sincerely Yours
Anbuselvam K

I have used below array formula in cell K6 to find out the missing number from given the minimum and maximum value in cell Q4 and R4,

{=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX($A:$A,$Q$4):INDEX($A:$A,$R$4)),SMALL(IFERROR(0+TRIM(MID(SUBSTITUTE(K8:K10895,"&",REPT(" ",30)),{1,30,60,90,120},30)),""),ROW(1:10895)),0)),ROW(INDEX($A:$A,$Q$4):INDEX($A:$A,$R$4))),ROW(A1)),"")}

Anyone suggest another formula like above which can find the highest numbers considering multiple number entries and range from 4001 to 5000 and also, with respect to dates in the adjecent rows.

Thanks in Advance.

Hi

The below formula also finding the highest values even with multiple entries like 12200&12201&12203. But, I need to find between 4001 to 5000 ranges.

Because this year invoice number starts with 4001 which is a lesser value than last year invoice number series like 12200 and so.

=AGGREGATE(14,6,--MID(SUBSTITUTE(K8:K10921,"&",REPT(" ",30)),{1,30,60,90,120},30),1)

Do the needful

Thanks in advance

Originally Posted by Keebellah

That is certainly an extra handicap

What did you mean?