Return next highest number using INDEX

[Llaindyr]

Hi all,

So this one may be a bit confusing to explain so I'll try my best! I'm on O365.

I have a table, which will grow by rows as time goes on.

ID1	ID2	ID3	ID4	ID5	ID6	ID7	ID8
1	2	aaa	bbb	ccc	ddd	100	100
2	2	eee	fff	ggg	hhh	400	400
3	3	iii	jjj	kkk	lll	700	700
4	4	eee	mmm	nnn	ooo	401	401

I also have a master list, that has a host of different numbers in it, with unique headers to identify what's in each column

100 Range	200 Range	300 Range	400 Range	500 Range	600 Range	700 Range	800 Range	900 Range	1000 Range
100	200	300	400	500	600	700	800	900	1000
101	201	301	401	501	601	701	801	901	1001
102	202	302	402	502	602	702	802	902	10

From there, I have one final table that's my 'lookup'

aaa	100 Range
eee	400 Range
iii	700 Range

Using the value in the ID3 column of 'eee', what I need is a formula that does the following

1. Checks what's in 'ID3' (eee), then looks up the 'lookup' to return a header (like a straight VLOOKUP) = 400 Range
2. Using the header, select the corresponding column of the 2nd table (like an INDEX MATCH) = 4th Column
3. Then, checks all the numbers in ID7 that exist to return the next highest number (like a MAX, maybe?) = 402

I can logically spell out how to do it, but for the life of me I can't figure out the correct syntax to get it to actually work.

Appreciate any and all help guys!

 

[jasonb75]

I ended up more than a little lost trying to navigate the steps of your logic, is this what you need?

Book1
	A	B	C	D	E	F	G	H	I	J
1	ID1	ID2	ID3	ID4	ID5	ID6	ID7	ID8
2	1	2	aaa	bbb	ccc	ddd	100	100
3	2	2	eee	fff	ggg	hhh	400	400
4	3	3	iii	jjj	kkk	lll	700	700
5	4	4	eee	mmm	nnn	ooo	401	401
6
7	100 Range	200 Range	300 Range	400 Range	500 Range	600 Range	700 Range	800 Range	900 Range	1000 Range
8	100	200	300	400	500	600	700	800	900	1000
9	101	201	301	401	501	601	701	801	901	1001
10	102	202	302	402	502	602	702	802	902	10
11
12	aaa	100	101
13	eee	400	402
14	iii	700	701
Sheet5

Cell Formulas
Range	Formula
B12:B14	B12	=INDEX($G$2:$G$5,MATCH(A12,$C$2:$C$5,0))
C12:C14	C12	=AGGREGATE(15,6,INDEX($A$8:$J$10,0,MATCH(B12&" Range",$A$7:$J$7,0))/(ISNA(MATCH(INDEX($A$8:$J$10,0,MATCH(B12&" Range",$A$7:$J$7,0)),$G$2:$G$5,0))),1)

 

[Llaindyr]

Ahh I see where what I said was a little confusing. Let me try posting it from Excel using cell references, so it might make more sense, and I'll include the logic I think should work, but I just can't get it right.

 

[jasonb75]

I think that is effectively what I had done, I just split the formula down as I thought range was based on the current ID7 value in the same row as 'eee'.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID1	ID2	ID3	ID4	ID5	ID6	ID7	ID8				aaa	100 Range
2	1	2	aaa	bbb	ccc	ddd	100	100				eee	400 Range
3	2	2	eee	fff	ggg	hhh	400	400				iii	700 Range
4	3	3	iii	jjj	kkk	lll	700	700
5	4	4	eee	mmm	nnn	ooo	401	401
6
7	100 Range	200 Range	300 Range	400 Range	500 Range	600 Range	700 Range	800 Range	900 Range	1000 Range
8	100	200	300	400	500	600	700	800	900	1000
9	101	201	301	401	501	601	701	801	901	1001
10	102	202	302	402	502	602	702	802	902	10
11
12			101
13			402
14			701
Sheet5

Cell Formulas
Range	Formula
C12:C14	C12	=AGGREGATE(15,6,INDEX($A$8:$J$10,0,MATCH(VLOOKUP(C2,$L$1:$M$3,2,0),$A$7:$J$7,0))/(ISNA(MATCH(INDEX($A$8:$J$10,0,MATCH(VLOOKUP(C2,$L$1:$M$3,2,0),$A$7:$J$7,0)),$G$2:$G$5,0))),1)

 

[Llaindyr]

Ok that absolutely works perfectly for what's described!

However, as the list will be more than 1 entry in cell C12 down, it's excluding if a number is assigned there. I.e. for cell C15, it would show 402 again, and not 403. Is there the possibility of inserting an IF/OR in there to also check C12 onward, or is it simply too complicated and I'll have to make it work another way?

 

[jasonb75]

It should be possible, I'll have another look at it when I get home later.

Does 400 range cover all numbers from 400 to 499, 700 range from 700 to 799, etc, or can there be numbers that are intentionally missed?

Also, is the formula actually going into a third table or is it being appended to the existing ID7 list in column G? A formula in G6 to return 402 should be much simpler than a formula in C12.

 

[Llaindyr]

Absolutely correct, they're 100 number ranges (400-499, etc.)

it will be going into a 3rd table, yeah. This is very rough, but yellow cells are where this formula would be going:

 

[jasonb75]

Dropping the second table, is this something that you could use?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID1	ID2	ID3	ID4	ID5	ID6	ID7	ID8				aaa	100
2	1	2	aaa	bbb	ccc	ddd	100	100				eee	400
3	2	2	eee	fff	ggg	hhh	400	400				iii	700
4	3	3	iii	jjj	kkk	lll	700	700
5	4	4	eee	mmm	nnn	ooo	401	401
6
7
8			aaa				101
9			eee				402
10			aaa				102
11			iii				701
12			eee				403
13			aaa				103
14			iii				702
Sheet5

Cell Formulas
Range	Formula
G8:G14	G8	=VLOOKUP(C8,$L$1:$M$3,2,0)+COUNTIFS(G$1:G7,">="&VLOOKUP(C8,$L$1:$M$3,2,0),$G$1:$G7,"<"&VLOOKUP(C8,$L$1:$M$3,2,0)+99)

 

[Llaindyr]

I don't think the VLookup will work because I believe it's working on the assumption that it's chronological, as it always looks at the first instance of the number and begins the 'countifs' from there.

I entered a new row so your cell is now on G9, however it updated itself to reflect this:

=VLOOKUP(C9,$L$1:$M$3,2,0)+COUNTIFS(G$1:G8,">="&VLOOKUP(C9,$L$1:$M$3,2,0),$G$1:$G8,"<"&VLOOKUP(C9,$L$1:$M$3,2,0)+99)

In the top table ID3 column, 'aaa' has the numbers as 102, 103, 101 in that order, however the bottom table starts at 103 then goes to 104 for the last row. It bypasses 100 altogether, and then also doesn't realise that 103 has already been used later on at G6.

The same applies for both the 400 and 700 ranges. The previous aggregate formula you used didn't have this issue, so

That was the reason for my thinking behind the (now removed) 2nd table. For table 3 tro look up what was already in Table 1, compare it to everything available in Table 2, then provide the next available number from the range (be it 400, 401 or 452, regardless of chronological order)

 

[jasonb75]

With the additional requirements of post 5 the aggregate method became unworkable, hence the alternative approach. The construct of the vlookup formula was based on inference not assumption. There was nothing to suggest that numbers could be omitted from earlier in the sequence, allowing for such things when not needed only makes the task more complicated than necessary.

The method below appears to work without the 2nd table, although this is again based on the current information and relies on the third table being directly below the first so that the ID7 entries for both are in one continuous column. Other layouts might be possible, but I think that it is going to make the formulas to complex to be practical.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID1	ID2	ID3	ID4	ID5	ID6	ID7	ID8				aaa	100
2	1	2	aaa	bbb	ccc	ddd	102	102				eee	400
3	2	2	eee	fff	ggg	hhh	401	401				iii	700
4	3	3	iii	jjj	kkk	lll	701	701
5	4	4	eee	mmm	nnn	ooo	402	402
6	5	5	aaa	bbb	ccc	ddd	103	103
7	1	1	aaa	bbb	ccc	ddd	101	101
8
9			aaa				100
10			eee				400
11			aaa				104
12			iii				700
13			eee				403
14			aaa				105
15			iii				702
Sheet5

Cell Formulas
Range	Formula
G9:G15	G9	=VLOOKUP(C9,$L$1:$M$3,2,0)+MATCH(TRUE,ISNA(MATCH(ROW(OFFSET(INDEX(G:G,VLOOKUP(C9,$L$1:$M$3,2,0)),,,100,1)),G$1:G8,0)),0)-1
Press CTRL+SHIFT+ENTER to enter array formulas.