# Return next highest number using INDEX

#### Llaindyr

##### New Member
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!

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### jasonb75

##### Well-known Member
I ended up more than a little lost trying to navigate the steps of your logic, is this what you need?
Book1
ABCDEFGHIJ
1ID1ID2ID3ID4ID5ID6ID7ID8
212aaabbbcccddd100100
322eeefffggghhh400400
433iiijjjkkklll700700
544eeemmmnnnooo401401
6
7100 Range200 Range300 Range400 Range500 Range600 Range700 Range800 Range900 Range1000 Range
81002003004005006007008009001000
91012013014015016017018019011001
1010220230240250260270280290210
11
12aaa100101
13eee400402
14iii700701
Sheet5
Cell Formulas
RangeFormula
B12:B14B12=INDEX(\$G\$2:\$G\$5,MATCH(A12,\$C\$2:\$C\$5,0))
C12:C14C12=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

##### New Member
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

##### Well-known Member
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
ABCDEFGHIJKLM
1ID1ID2ID3ID4ID5ID6ID7ID8aaa100 Range
212aaabbbcccddd100100eee400 Range
322eeefffggghhh400400iii700 Range
433iiijjjkkklll700700
544eeemmmnnnooo401401
6
7100 Range200 Range300 Range400 Range500 Range600 Range700 Range800 Range900 Range1000 Range
81002003004005006007008009001000
91012013014015016017018019011001
1010220230240250260270280290210
11
12101
13402
14701
Sheet5
Cell Formulas
RangeFormula
C12:C14C12=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

##### New Member

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

##### Well-known Member
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

##### New Member

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

##### Well-known Member
Dropping the second table, is this something that you could use?
Book1
ABCDEFGHIJKLM
1ID1ID2ID3ID4ID5ID6ID7ID8aaa100
212aaabbbcccddd100100eee400
322eeefffggghhh400400iii700
433iiijjjkkklll700700
544eeemmmnnnooo401401
6
7
8aaa101
9eee402
10aaa102
11iii701
12eee403
13aaa103
14iii702
Sheet5
Cell Formulas
RangeFormula
G8:G14G8=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

##### New Member
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:

Excel Formula:
``=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

##### Well-known Member
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
ABCDEFGHIJKLM
1ID1ID2ID3ID4ID5ID6ID7ID8aaa100
212aaabbbcccddd102102eee400
322eeefffggghhh401401iii700
433iiijjjkkklll701701
544eeemmmnnnooo402402
655aaabbbcccddd103103
711aaabbbcccddd101101
8
9aaa100
10eee400
11aaa104
12iii700
13eee403
14aaa105
15iii702
Sheet5
Cell Formulas
RangeFormula
G9:G15G9=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.

Replies
8
Views
154
Replies
4
Views
103
Replies
0
Views
90
Replies
3
Views
179
Replies
3
Views
124