Return Value if between Numbers in table, and if other cell matches row in Table?

[futureexcelguru]

Hi everyone, I keep finding formulas online that match only one requirement and not all combined.
I have data in a table like so
Column A: Policy
Column B: Starting Range (#)
Column C: Ending Range (#)
Column D: Tier to Assign

In another cell based on the years of service and policy assignment per employee, I need a formula to kick out which Tier that employee would fall under. Theres about 20 policies and the ranges vary per policy. Here is a smaller simplified snapshot of a sample .. any help is appreciated! I'm relatively new to complex formulas so i'm sure its something i'm completely just not thinking of.

Only value i'm looking to return is H10 from the table to the left based on H8 and H9

 

[cyrilbrd]

Hi, maybe something along:

Data.xlsx
	A	B	C
1	Desc	YOS	Tier
2	PA	0	1
3	PA	3	2
4	PB	0	1
5	PB	3	2
6	PB	9	3
7	PD	0	1
8
9
10
11	Policy	PB
12	YOS	5
13	Tier	2
Futureexcelguru

Cell Formulas
Range	Formula
B13	B13	=INDEX(Table5[Tier],MATCH(B12,IF(Table5[Desc]=B11,Table5[YOS]),1))

 

[Fluff]

Another option

=SUMIFS(D:D,A:A,H9,B:B,"<="&H8,C:C,">="&H8)