Return value if condition is met, includes lookup value

[Sirod]

Hello,

I have two different tables, one that has a list of levels and the middle 50% of point ranges associated with each level - see the below sample

Level	Lower Point	Upper Point
4	425	475
3	325	375
2	225	275
1	1250	175

The second table has a list of players, their total score, and level. I need a formula that will look up the level in table one and return a Yes if the total score falls within the lower and upper point ranges noted for that level.

For example, if a player was at level 4 and had a score of 450, Yes would be returned. But if the score was 411, a no would be returned.

Please note that the data must remain sorted by player name. The levels and scores are not sorted and I can't change this.

Thanks.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[awoohaw]

is the 1250 lowest point for level 1 supposed to be 125?

Try this:

Book1
	A	B	C
1	Level	Lower Point	Upper Point
2	4	425	475
3	3	325	375
4	2	225	275
5	1	125	175
6
7
8
9	Player	Score	withing Limits
10	3	498	No
11	1	179	No
12	3	400	No
13	4	336	No
14	1	412	No
15	3	358	Yes
16	1	416	No
17	2	431	No
18	3	351	Yes
19	3	202	No
20	1	474	No
21	1	550	No
22	3	481	No
23	4	435	Yes
24	4	306	No
sirod

Cell Formulas
Range	Formula
C10:C24	C10	=IF(SUM((A10=$A$2:$A$5)*(B10>=$B$2:$B$5)*(B10<=$C$2:$C$5))>0,"Yes","No")

 

[Sirod]

is the 1250 lowest point for level 1 supposed to be 125?

Try this:

Book1
	A	B	C
1	Level	Lower Point	Upper Point
2	4	425	475
3	3	325	375
4	2	225	275
5	1	125	175
6
7
8
9	Player	Score	withing Limits
10	3	498	No
11	1	179	No
12	3	400	No
13	4	336	No
14	1	412	No
15	3	358	Yes
16	1	416	No
17	2	431	No
18	3	351	Yes
19	3	202	No
20	1	474	No
21	1	550	No
22	3	481	No
23	4	435	Yes
24	4	306	No
sirod

Cell Formulas
Range	Formula
C10:C24	C10	=IF(SUM((A10=$A$2:$A$5)*(B10>=$B$2:$B$5)*(B10<=$C$2:$C$5))>0,"Yes","No")

This worked perfectly. Thank you!

 

[Fluff]

@Sirod, any chance you could answer my question & update your profile?
There may be better ways of doing this.