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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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)
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top