Using VLOOKUP with multiple criteria and 'banded' results

[Flave]

Hi all, first post here and I’m struggling with this and would really appreciate any help. I’ll be very impressed if anyone can help me use a formula or another non-VBA technique to solve.


I have two tables to run the lookup from – one actual and one with a variable linking to the reference table. There are three variables which dictate this and step 1: these need to be matched such that -
condition1, condition2 and condition3 are all met.

	A	B	C	D
1	Table 1
2	x	y	z	variable
3	condition1	condition2	condition3	50

<tbody>
</tbody>

	A	B	C	D
1	Table 2
2	x	y	z	bracket
3	condition1	condition2	condition3	100

<tbody>
</tbody>

Table 2 gives the bracket (explained below) and table 1 gives a value to match. The value I want returned has a problem:


So I have the following table, the reference table, which denotes pricing brackets, the ‘bracket’ means the MAX value which corresponds to the right column

Bracket	Value
15	100
20	250
25	280
30	300
35	460
40	500
50	650
60	700
70	800
75	850
80	900
100	1000

<tbody>
</tbody>

So e.g. in a vlookup scenario, ‘20’ in the left column means all numbers <= 20 so ‘1’ can return 250, and similarly ‘100’ means all numbers <=100, so ‘1’ could also return 1000. To be clear, the information in this table/this bracket system cannot be changed.


Taking the above Table 1/Table 2 example, I want to know how I would get the lookup to return 1000 (from the reference table) as the value for ‘50’ in Table 1 (rather than 650 from the reference table).



So the way I’ve tried it is to concatenate the three conditions x, y and z on both tables and try to vlookup (with ‘TRUE’) from there. What I need is for the variable to be picked up and correctly matched to the reference table but I just can’t get it right!

Any ideas?

 

[Flave]

Thanks Rich! I'll test this in a sec, could you quickly outline how you've laid out the VLOOKUP reference tables please?:

"B58:F58,5,0" and "A39:B50,2,0"

 

[RCBricker]

b58:f58 is table 2 the ,5, is the column number with the bracket info

A39:B50 is the reference table and the ,2, is the value from that table

rich

 

[Flave]

hmm, looks close but not quite there. I did change the reference table to include the 100 but still get no match - I'm guessing the green area is the problem perhaps? Really appreciate your effort with this Rich.

 

[RCBricker]

based on the screen shot. your reference table does not include 100 as part of the range in the formula. (it is not inside the pink square)

you also have no spot where you are matching the concatenation from the first table

in my formula column B of table two has a value that equals the concatenation of conditions 1-3. SO the vlookup finds the value there and then returns the value of the bracket.

 

[Flave]

I did try it with the 100 included but still got no match. Are you able to provide a screenshot of your set up for comparison?

 

[Aladin Akyurek]

hmm, looks close but not quite there. I did change the reference table to include the 100 but still get no match - I'm guessing the green area is the problem perhaps? Really appreciate your effort with this Rich.

Try to post the sample the image shows using one of the methods of posting directly on this forum: http://www.mrexcel.com/forum/about-board/508133-attachments.html or
https://app.box.com/s/soezox25h3w0q5s4rcyl.

 

[Flave]

based on the screen shot. your reference table does not include 100 as part of the range in the formula. (it is not inside the pink square)

you also have no spot where you are matching the concatenation from the first table

in my formula column B of table two has a value that equals the concatenation of conditions 1-3. SO the vlookup finds the value there and then returns the value of the bracket.

Thank you! I tweaked my cells a bit and got it working. Great stuff Rich.

 

[RCBricker]

Glad you got it working.

Rich