Flave
New Member
- Joined
- Jul 14, 2015
- Messages
- 13
Hi all,
I had a related question recently that was brilliantly answered using multiple VLOOKUPs (credit to RCBricker), I now have a slightly different problem but the base data is quite similar – I’ve tried to include more detail this time but this thread - http://www.mrexcel.com/forum/excel-...ookup-multiple-criteria-banded-results-2.html - can be good for a bit of background (although it isn’t necessary, please read on).
Non-VBA solution is very much preferred and I’m sure it must be possible. The data shown is a sample of several 100,000 rows.
Now I have two tables (below) and the objective is to get the figures for S-numb as found from Table 2. The variables in ABC (BCD in Table 2) all need to match in both tables before commencing the lookup, which can be done using concatenate (I’ve added this to column A of the data in Table 2)
E3:E10 (Table 1) is basically using RCBricker‘s formula to get the lookup going
TABLE 1 -
[TABLE="width: 392"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]NumA
[/TD]
[TD]Code
[/TD]
[TD]IDN
[/TD]
[TD]Bracket
[/TD]
[TD]S-numb
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a023
[/TD]
[TD]500
[/TD]
[TD]=VLOOKUP(VlOOKUP(CONC…
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a233
[/TD]
[TD]1020
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a413
[/TD]
[TD]4242
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]500
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]84
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]400
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]200
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]884
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
TABLE 2 –
[TABLE="width: 484"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]LOOKUP
[/TD]
[TD]NumA
[/TD]
[TD]Code
[/TD]
[TD]IDN
[/TD]
[TD]S-numb
[/TD]
[TD]Bracket
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]55201St1a023
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a023
[/TD]
[TD]38
[/TD]
[TD]10000
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]55201St1a233
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a233
[/TD]
[TD]78
[/TD]
[TD]10000
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]55019St2a413
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a413
[/TD]
[TD]145
[/TD]
[TD]10000
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]55019St2a910
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]163
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]55019St2a910
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]181
[/TD]
[TD]10000
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]55019St2a941
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]199
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]55019St2a941
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]224.5
[/TD]
[TD]550
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]55019St2a941
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]21.3
[/TD]
[TD]999
[/TD]
[/TR]
</tbody>[/TABLE]
How brackets work:
The brackets in Table 2 tells me the S-numb, these brackets are MAX values. So F6, Table 2 for example is 450, meaning any number in a bracket (Table 1) between 1-450 should point to that.
Because of this, the problem is having multiple hits on the Vlookup and needing it to take the right criteria and return the correct value. Because there can be several brackets against the 3 variables, using something like ‘MAX()’doesn’t work.
Note – I’ve kept the examples mostly in parallel row order here for easy reading but the rows are mixed in the data, hence the choice of VLOOKUP
As such, what I need is the following sort of results –
RESULTS TAB –
[TABLE="width: 392"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]NumA
[/TD]
[TD]Code
[/TD]
[TD]IDN
[/TD]
[TD]Bracket
[/TD]
[TD]S-numb
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a023
[/TD]
[TD]500
[/TD]
[TD]38
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a233
[/TD]
[TD]1020
[/TD]
[TD]78
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a413
[/TD]
[TD]4242
[/TD]
[TD]145
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]500
[/TD]
[TD]181
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]84
[/TD]
[TD]163
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]400
[/TD]
[TD]224.5
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]200
[/TD]
[TD]199
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]884
[/TD]
[TD]21.3
[/TD]
[/TR]
</tbody>[/TABLE]
Here, you can see something like D5 is easy because its bracket of 4242 is well within the lookup MAX value bracket of 10000 in Table 2 and therefore returns the correct S-numb of 145. The criteria based ones that I can’t get to work are in D6:D7 and D8:D10
Taking D8 for example, the 400 needs to go through the following options in its calculation from Table 2:
[TABLE="width: 131"]
<tbody>[TR]
[TD]1-200
[/TD]
[TD]199
[/TD]
[/TR]
[TR]
[TD]201-550
[/TD]
[TD]224.5
[/TD]
[/TR]
[TR]
[TD]551-999
[/TD]
[TD]21.3
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
To correctly select 224.5 as the s-numb.
The way I currently have it, the VLOOKUP just returns the first result it can find after the concatenate without searching for criteria. Something like;
FAIL TAB –
[TABLE="width: 392"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]NumA
[/TD]
[TD]Code
[/TD]
[TD]IDN
[/TD]
[TD]Bracket
[/TD]
[TD]S-numb
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a023
[/TD]
[TD]500
[/TD]
[TD]38
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a233
[/TD]
[TD]1020
[/TD]
[TD]78
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a413
[/TD]
[TD]4242
[/TD]
[TD]145
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]500
[/TD]
[TD]163
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]84
[/TD]
[TD]163
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]400
[/TD]
[TD]199
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]200
[/TD]
[TD]199
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]884
[/TD]
[TD]199
[/TD]
[/TR]
</tbody>[/TABLE]
Any help on getting the Results Tab ouput please, I’m struggling with this!
I had a related question recently that was brilliantly answered using multiple VLOOKUPs (credit to RCBricker), I now have a slightly different problem but the base data is quite similar – I’ve tried to include more detail this time but this thread - http://www.mrexcel.com/forum/excel-...ookup-multiple-criteria-banded-results-2.html - can be good for a bit of background (although it isn’t necessary, please read on).
Non-VBA solution is very much preferred and I’m sure it must be possible. The data shown is a sample of several 100,000 rows.
Now I have two tables (below) and the objective is to get the figures for S-numb as found from Table 2. The variables in ABC (BCD in Table 2) all need to match in both tables before commencing the lookup, which can be done using concatenate (I’ve added this to column A of the data in Table 2)
E3:E10 (Table 1) is basically using RCBricker‘s formula to get the lookup going
TABLE 1 -
[TABLE="width: 392"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]NumA
[/TD]
[TD]Code
[/TD]
[TD]IDN
[/TD]
[TD]Bracket
[/TD]
[TD]S-numb
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a023
[/TD]
[TD]500
[/TD]
[TD]=VLOOKUP(VlOOKUP(CONC…
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a233
[/TD]
[TD]1020
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a413
[/TD]
[TD]4242
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]500
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]84
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]400
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]200
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]884
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
TABLE 2 –
[TABLE="width: 484"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]LOOKUP
[/TD]
[TD]NumA
[/TD]
[TD]Code
[/TD]
[TD]IDN
[/TD]
[TD]S-numb
[/TD]
[TD]Bracket
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]55201St1a023
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a023
[/TD]
[TD]38
[/TD]
[TD]10000
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]55201St1a233
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a233
[/TD]
[TD]78
[/TD]
[TD]10000
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]55019St2a413
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a413
[/TD]
[TD]145
[/TD]
[TD]10000
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]55019St2a910
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]163
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]55019St2a910
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]181
[/TD]
[TD]10000
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]55019St2a941
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]199
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]55019St2a941
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]224.5
[/TD]
[TD]550
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]55019St2a941
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]21.3
[/TD]
[TD]999
[/TD]
[/TR]
</tbody>[/TABLE]
How brackets work:
The brackets in Table 2 tells me the S-numb, these brackets are MAX values. So F6, Table 2 for example is 450, meaning any number in a bracket (Table 1) between 1-450 should point to that.
Because of this, the problem is having multiple hits on the Vlookup and needing it to take the right criteria and return the correct value. Because there can be several brackets against the 3 variables, using something like ‘MAX()’doesn’t work.
Note – I’ve kept the examples mostly in parallel row order here for easy reading but the rows are mixed in the data, hence the choice of VLOOKUP
As such, what I need is the following sort of results –
RESULTS TAB –
[TABLE="width: 392"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]NumA
[/TD]
[TD]Code
[/TD]
[TD]IDN
[/TD]
[TD]Bracket
[/TD]
[TD]S-numb
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a023
[/TD]
[TD]500
[/TD]
[TD]38
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a233
[/TD]
[TD]1020
[/TD]
[TD]78
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a413
[/TD]
[TD]4242
[/TD]
[TD]145
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]500
[/TD]
[TD]181
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]84
[/TD]
[TD]163
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]400
[/TD]
[TD]224.5
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]200
[/TD]
[TD]199
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]884
[/TD]
[TD]21.3
[/TD]
[/TR]
</tbody>[/TABLE]
Here, you can see something like D5 is easy because its bracket of 4242 is well within the lookup MAX value bracket of 10000 in Table 2 and therefore returns the correct S-numb of 145. The criteria based ones that I can’t get to work are in D6:D7 and D8:D10
Taking D8 for example, the 400 needs to go through the following options in its calculation from Table 2:
[TABLE="width: 131"]
<tbody>[TR]
[TD]1-200
[/TD]
[TD]199
[/TD]
[/TR]
[TR]
[TD]201-550
[/TD]
[TD]224.5
[/TD]
[/TR]
[TR]
[TD]551-999
[/TD]
[TD]21.3
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
To correctly select 224.5 as the s-numb.
The way I currently have it, the VLOOKUP just returns the first result it can find after the concatenate without searching for criteria. Something like;
FAIL TAB –
[TABLE="width: 392"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]NumA
[/TD]
[TD]Code
[/TD]
[TD]IDN
[/TD]
[TD]Bracket
[/TD]
[TD]S-numb
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a023
[/TD]
[TD]500
[/TD]
[TD]38
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]55201
[/TD]
[TD]St1
[/TD]
[TD]a233
[/TD]
[TD]1020
[/TD]
[TD]78
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a413
[/TD]
[TD]4242
[/TD]
[TD]145
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]500
[/TD]
[TD]163
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a910
[/TD]
[TD]84
[/TD]
[TD]163
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]400
[/TD]
[TD]199
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]200
[/TD]
[TD]199
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]55019
[/TD]
[TD]St2
[/TD]
[TD]a941
[/TD]
[TD]884
[/TD]
[TD]199
[/TD]
[/TR]
</tbody>[/TABLE]
Any help on getting the Results Tab ouput please, I’m struggling with this!