Formula help

[dani1]

Data Table
[TABLE="width: 223"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Tier[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD]officer[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]Management Associate[/TD]
[TD="align: right"]30000[/TD]
[/TR]
[TR]
[TD]Deputy Manager[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]Senior Manager[/TD]
[TD="align: right"]120000[/TD]
[/TR]
[TR]
[TD]Manager Level 2[/TD]
[TD="align: right"]80000[/TD]
[/TR]
[TR]
[TD]Manager Level 1[/TD]
[TD="align: right"]64000[/TD]
[/TR]
[TR]
[TD]Assistant Manager[/TD]
[TD="align: right"]25000[/TD]
[/TR]
[TR]
[TD]General Manager[/TD]
[TD="align: right"]160000[/TD]
[/TR]
[TR]
[TD]Senior General Manager[/TD]
[TD="align: right"]145000[/TD]
[/TR]
[TR]
[TD]Management Associate[/TD]
[TD="align: right"]35000[/TD]
[/TR]
</tbody>[/TABLE]


Criteria Table

[TABLE="width: 287"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Cadre[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[/TR]
[TR]
[TD]Senior General Manager[/TD]
[TD="align: right"]140000[/TD]
[TD="align: right"]260000[/TD]
[/TR]
[TR]
[TD]General Manager[/TD]
[TD="align: right"]110000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD]Senior Manager[/TD]
[TD="align: right"]90000[/TD]
[TD="align: right"]125000[/TD]
[/TR]
[TR]
[TD]Manager Level 1[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD]Manager Level 2[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]75000[/TD]
[/TR]
[TR]
[TD]Deputy Manager[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]60000[/TD]
[/TR]
[TR]
[TD]Assistant Manager[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]45000[/TD]
[/TR]
[TR]
[TD]Management Associate[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]30000[/TD]
[/TR]
[TR]
[TD]Officer[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]20000[/TD]
[/TR]
</tbody>[/TABLE]


i have two table above i want formula in 1st table according to criteria for example
if officer comes with salary range 10,000 to 20,000 it must show "in range" if greater than 20,000 then it must show "High" and if it shows less than 10000 than it must show "Low" and so on for management associate and others tier.


Please help me in case of confusion please let me know

 

[AlanY]

try this

Excel 2012
	A	B	C	D
1	Data Table
2	Tier	Salary
3	officer	10000		In Range
4	Management Associate	30000		In Range
5	Deputy Manager	50000		In Range
6	Senior Manager	120000		In Range
7	Manager Level 2	80000		High
8	Manager Level 1	64000		Low
9	Assistant Manager	25000		Low
10	General Manager	160000		High
11	Senior General Manager	145000		In Range
12	Management Associate	35000		High
13
14
15	Criteria Table
16
17	Cadre	Min	Max
18	Senior General Manager	140000	260000
19	General Manager	110000	150000
20	Senior Manager	90000	125000
21	Manager Level 1	65000	100000
22	Manager Level 2	50000	75000
23	Deputy Manager	40000	60000
24	Assistant Manager	30000	45000
25	Management Associate	15000	30000
26	Officer	10000	20000
Sheet1

Cell Formulas
Range	Formula
D3	=IF(B3$B$18:$B$26,MATCH(A3,$A$18:$A$26,0)),"Low",IF(B3>INDEX($C$18:$C$26,MATCH(A3,$A$18:$A$26,0)),"High","In Range"))