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.

Color	Invoice $	Tier
Blue	3
Blue	17
Black	571
Yellow	81
Black	1026
Blue	1
Yellow	97
Yellow	175
Black	386

<tbody>
</tbody>

Color	Min	Max	Tier
Black	0	500	Tier 3
Black	500	1000	Tier 2
Black	1000		Tier 1
Yellow	0	80	Tier 3
Yellow	80	160	Tier 2
Yellow	160		Tier 1
Blue	0	50	Tier 3
Blue	50	100	Tier 2
Blue	100		Tier 1

<tbody>
</tbody>

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.

Color	Invoice $	Tier
Blue	3
Blue	17
Black	571
Yellow	81
Black	1026
Blue	1
Yellow	97
Yellow	175
Black	386

<tbody>
</tbody>

Color	Min	Max	Tier
Black	0	500	Tier 3
Black	500	1000	Tier 2
Black	1000		Tier 1
Yellow	0	80	Tier 3
Yellow	80	160	Tier 2
Yellow	160		Tier 1
Blue	0	50	Tier 3
Blue	50	100	Tier 2
Blue	100		Tier 1

<tbody>
</tbody>

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