Llaindyr

New Member
Joined
Oct 22, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

ID1ID2ID3ID4ID5ID6ID7ID8
12aaabbbcccddd100100
22eeefffggghhh400400
33iiijjjkkklll700700
44eeemmmnnnooo401401

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 Range200 Range300 Range400 Range500 Range600 Range700 Range800 Range900 Range1000 Range
1002003004005006007008009001000
1012013014015016017018019011001
10220230240250260270280290210

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

aaa100 Range
eee400 Range
iii700 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!
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 22, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

Capture.PNG
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 22, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 22, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

Capture.PNG
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 22, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

1603587981210.png


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
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,571
Messages
5,548,843
Members
410,877
Latest member
RaeB
Top