Using VLOOKUP with a Criteria / Range

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!
 
With the bracketed results, the table being lookup must be sorted. Since you are usng the combined data Sort by A then by D
 
Upvote 0
Thanks SpillerBD, I don't think sorting in this case will do anything as the vlookup will still pick the first result returned.
 
Upvote 0
Would some sort of array match index work here. I've never used those functions so am not familiar but google has shown some people do that to get vlookup matching a set criteria. Can anyone assist me please?
 
Upvote 0
Thanks SpillerBD, I don't think sorting in this case will do anything as the vlookup will still pick the first result returned.
I think it is sorting, but all the fields you are referencing will need to be concatenated together. The KEY would be all the fields concatenated MUST BE the same length.
The other issue may be if number's stored as text (or other data typing issues.)
If you could share a sample file that would give us better clarity with your data.
 
Upvote 0
It isn't letting me attach docs here. My VLOOKUP also references a sheet with

1 - 50
2 - 50
3 - 50
etc..
50 - 50
51 - 200
52 - 200
etc..
200 - 200
etc...

where 50 is the first bracket and 200 is the second and so on. But obviously, 200 can also include values of 50 or less too so I'm starting to think VLOOKUP won't work at all. This might just be too tough / not possible. :(

Appreciate the help.
 
Upvote 0

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