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.

[TABLE="width: 343"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Table 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]x
[/TD]
[TD]y
[/TD]
[TD]z
[/TD]
[TD]variable
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]condition1
[/TD]
[TD]condition2
[/TD]
[TD]condition3
[/TD]
[TD]50
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 343"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Table 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]x
[/TD]
[TD]y
[/TD]
[TD]z
[/TD]
[TD]bracket
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]condition1
[/TD]
[TD]condition2
[/TD]
[TD]condition3
[/TD]
[TD]100
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 131"]
<tbody>[TR]
[TD]Bracket
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]250
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]280
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]460
[/TD]
[/TR]
[TR]
[TD]40
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]50
[/TD]
[TD]650
[/TD]
[/TR]
[TR]
[TD]60
[/TD]
[TD]700
[/TD]
[/TR]
[TR]
[TD]70
[/TD]
[TD]800
[/TD]
[/TR]
[TR]
[TD]75
[/TD]
[TD]850
[/TD]
[/TR]
[TR]
[TD]80
[/TD]
[TD]900
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]1000
[/TD]
[/TR]
</tbody>[/TABLE]


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?

 

[RCBricker]

ok lets get some clarity.

First meeting three criteria is easy. simple IF AND Statement.

How is the variable 50 determined?

next your table two has conditions 1 2 & 3. Are they the same conditions from table 1? How is the bracket determined?

Rich

 

[Flave]

Thanks Rich.

Table 1 and 2 as here are me simplifying and giving a small section. The actual data is all extracted from an external source and both Table 1 and 2 have 400,000+ rows each in the same sort of layout as above. As such, table 1 and 2 are naturally on seperate sheets. I'm not sure if IF(AND.. ) will work given these two tables present results in different order, hence the choice to go with VLOOKUP.

Variable was probably the wrong word by me. There are NO calculations, this is a straight data extract.

Yes, in a successful match - condition1, condition2 and condition3 would be the same in both tables. So wh I've done using CONCATENATE is:
- condition1condition2condition3 (Table1) successfully matches ondition1condition2condition3 (Table2) using VLOOKUP

I now need a way to get the 50 and 100 to somehow link to the reference table to make sure the 50 returns 1000 (as per the reference table) as the 100 bracket in Table 2 is the only row where the 3 conditions meet. Again, the brackets are non-variable and are 100% set in stone extracted externally.

Hope that helps and thanks in advanced.

 

[RCBricker]

A little

Ok table one condition 1 2 & 3 must all be present and they must also match to condition 1 2 & 3 of table two. If all of that happens then we have a match and move to get a value from the reference table? correct?

also...

concatenate is being used to match table 1 line to table 2 line correct? So it would be the same if Column 4 had a unique name in it.

Correct?

 

[RCBricker]

why the variable in table 1?

What happens if the variable in table 1 is greater than the bracket number in table 2?

is any value returned for the bracket number in table 2?

Rich

 

[Flave]

Correct, conditions1,2,3 are all present in all rows of data. They just need to be the same 3 in Table 1 and Table 2.

Once these three are a confirmed match, we're just interested in resolving the "D3" values in the original post (the formula I'm looking for should only output something on the Table 1 sheet) - Table 1 needs to return a 'Value' using a combination of information from Table 2 and the reference table:

- Table 2 tells us the matched bracket - in this case it is bracket '100'

- The Reference Table tells us the Value we want to output and the only number the bracket '100' is connected to.


So say I had a seperate example with conditionAconditionBconditionC in Table 1 matching conditionAconditionBconditionC in Table 2. Table 1 would have the 'variable' in D3 as 50 but this time I'd see the bracket in Table 2 as being 50 also. In such a scenario, I'd go to the reference table and would need the value 650.

 

[Flave]

why the variable in table 1?

What happens if the variable in table 1 is greater than the bracket number in table 2?

is any value returned for the bracket number in table 2?

Rich

The variable in Table 1 is only going to be one of 12 numbers (those in the left column of the reference table). The difficulty is in the fact that 100 can account for a Table 1 variable of say '20' as 100 is a MAX value.

The bracket value in Table 2 will again be one of the 12 numbers in the left column of the reference table. Brackets in Table 2 can be higher than those in Table 1 but not vica versa.

 

[Aladin Akyurek]

Care to provide a non-abstract sample along with the result that must obtain?

 

[RCBricker]

try this

=IF(ISERROR(VLOOKUP(VLOOKUP(CONCATENATE(B66,C66,D66),B58:F58,5,0),A39:B50,2,0)),"No Match",VLOOKUP(VLOOKUP(CONCATENATE(B66,C66,D66),B58:F58,5,0),A39:B50,2,0))

the if is error portion just helps with errors.

the first vlookup gives us the value to use in the second. So when all three criteria are met and found on table 2 the bracket value is used to lookup the value from the reference table


HTH

Rich

 

[RCBricker]

The variable in Table 1 is only going to be one of 12 numbers (those in the left column of the reference table). The difficulty is in the fact that 100 can account for a Table 1 variable of say '20' as 100 is a MAX value.

The bracket value in Table 2 will again be one of the 12 numbers in the left column of the reference table. Brackets in Table 2 can be higher than those in Table 1 but not vica versa.

I skipped all that as I assume that the bracket value is a value that can be returned from table 2 if there is a match from the table 1 lookup.

rich