Range VLOOKUP with Criteria to determine lookup table

[OverR4ted]

I am trying to use a Range VLOOKUP based upon the criteria of color in this example. I have another set of data but used this example for simplicity and business reasons. But I need to use the table range that corresponds with the color. I have a lot of values so hard coding is not an option. Basically I need to come up with the Tier level in table 1. I also want to be able to change the Min Max values and see how that balances the numbers for customers in each category. Any help would be great as I have a very large set of data I am working with.

[TABLE="class: outer_border, width: 250"]
<tbody>[TR]
[TD]Color[/TD]
[TD]Invoice $[/TD]
[TD]Tier[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]571[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]1026[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]97[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]175[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]386[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 250"]
<tbody>[TR]
[TD]Color[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Tier[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]Tier 3[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[TD]Tier 2[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]Tier 1
[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]0[/TD]
[TD]80[/TD]
[TD]Tier 3[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]80[/TD]
[TD]160[/TD]
[TD]Tier 2[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]160[/TD]
[TD][/TD]
[TD]Tier 1[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]Tier 3[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]Tier 2[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]Tier 1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much for your time and effort!

 

[jarjarbingie]

I am trying to use a Range VLOOKUP based upon the criteria of color in this example. I have another set of data but used this example for simplicity and business reasons. But I need to use the table range that corresponds with the color. I have a lot of values so hard coding is not an option. Basically I need to come up with the Tier level in table 1. I also want to be able to change the Min Max values and see how that balances the numbers for customers in each category. Any help would be great as I have a very large set of data I am working with.

[TABLE="class: outer_border, width: 250"]
<tbody>[TR]
[TD]Color[/TD]
[TD]Invoice $[/TD]
[TD]Tier[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]571[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]1026[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]97[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]175[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]386[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 250"]
<tbody>[TR]
[TD]Color[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Tier[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]Tier 3[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[TD]Tier 2[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]Tier 1[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]0[/TD]
[TD]80[/TD]
[TD]Tier 3[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]80[/TD]
[TD]160[/TD]
[TD]Tier 2[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]160[/TD]
[TD][/TD]
[TD]Tier 1[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]Tier 3[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]Tier 2[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]Tier 1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much for your time and effort!

assuming table 1 is in Sheet1 and table 2 is in Sheet 2:

C2=INDEX(OFFSET(Sheet2!$D$1,MATCH($A2,Sheet2!$A$2:$A$10,0),0,3,1),MATCH($B2,OFFSET(Sheet2!$B$1,MATCH($A2,Sheet2!$A$2:$A$10,0),0,3,1),1))

 

[SyedUsman]

=LOOKUP(B2,IF(A2=$H$2:$H$10,$I$2:$K$10))

assuming the table 1 is in cells A1:C10 meanwhile Table 2 is in H1:K10

 

[SyedUsman]

=LOOKUP(B2,IF(A2=$H$2:$H$10,$I$2:$K$10))

assuming the table 1 is in cells A1:C10 meanwhile Table 2 is in H1:K10

Use with CTRL+SHIFT+ENTER

 

[FormR]

Just another option..:

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Color	Invoice $	Tier		Color	Min	Max	Tier
2	Blue	50	Tier 2		Black	0	500	Tier 3
3	Blue	17	Tier 3		Black	500	1000	Tier 2
4	Black	571	Tier 2		Black	1000		Tier 1
5	Yellow	81	Tier 2		Yellow	0	80	Tier 3
6	Black	1026	Tier 1		Yellow	80	160	Tier 2
7	Blue	1	Tier 3		Yellow	160		Tier 1
8	Yellow	97	Tier 2		Blue	0	50	Tier 3
9	Yellow	175	Tier 1		Blue	50	100	Tier 2
10	Black	386	Tier 3		Blue	100		Tier 1
Sheet1

Cell Formulas
Range	Formula
C2	=LOOKUP(B2,$F$2:$F$10/($E$2:$E$10=A2),$H$2:$H$10)

 

[OverR4ted]

SyedUsman,

This was the simplest solution to use because of the simplicity. Thank you for taking care of this for me, works like a charm.

-OverR4ted