Using VLOOKUP with a Criteria / Range

[Flave]

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 -

1	A	B	C	D	E
2	NumA	Code	IDN	Bracket	S-numb
3	55201	St1	a023	500	=VLOOKUP(VlOOKUP(CONC…
4	55201	St1	a233	1020
5	55019	St2	a413	4242
6	55019	St2	a910	500
7	55019	St2	a910	84
8	55019	St2	a941	400
9	55019	St2	a941	200
10	55019	St2	a941	884

<tbody>
</tbody>

TABLE 2 –

1	A	B	C	D	E	F
2	LOOKUP	NumA	Code	IDN	S-numb	Bracket
3	55201St1a023	55201	St1	a023	38	10000
4	55201St1a233	55201	St1	a233	78	10000
5	55019St2a413	55019	St2	a413	145	10000
6	55019St2a910	55019	St2	a910	163	450
7	55019St2a910	55019	St2	a910	181	10000
8	55019St2a941	55019	St2	a941	199	200
9	55019St2a941	55019	St2	a941	224.5	550
10	55019St2a941	55019	St2	a941	21.3	999

<tbody>
</tbody>

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 –

1	A	B	C	D	E
2	NumA	Code	IDN	Bracket	S-numb
3	55201	St1	a023	500	38
4	55201	St1	a233	1020	78
5	55019	St2	a413	4242	145
6	55019	St2	a910	500	181
7	55019	St2	a910	84	163
8	55019	St2	a941	400	224.5
9	55019	St2	a941	200	199
10	55019	St2	a941	884	21.3

<tbody>
</tbody>

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:

1-200	199
201-550	224.5
551-999	21.3

<tbody>
</tbody>

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 –

1	A	B	C	D	E
2	NumA	Code	IDN	Bracket	S-numb
3	55201	St1	a023	500	38
4	55201	St1	a233	1020	78
5	55019	St2	a413	4242	145
6	55019	St2	a910	500	163
7	55019	St2	a910	84	163
8	55019	St2	a941	400	199
9	55019	St2	a941	200	199
10	55019	St2	a941	884	199

<tbody>
</tbody>

Any help on getting the Results Tab ouput please, I’m struggling with this!

 

[SpillerBD]

With the bracketed results, the table being lookup must be sorted. Since you are usng the combined data Sort by A then by D

 

[Flave]

Thanks SpillerBD, I don't think sorting in this case will do anything as the vlookup will still pick the first result returned.

 

[Flave]

Any other ideas?

 

[Flave]

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?

 

[SpillerBD]

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.

 

[Flave]

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.