Flave
New Member
- Joined
- Jul 14, 2015
- Messages
- 13
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.
<tbody>
</tbody>
<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
<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?
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?