IF AND/OR formula issues

[Ketterj]

Team,
First - thank you for taking a look at this. Secondly, I am struggling to get Excel to return the correct values for the given data set below that corresponds to the two tables. I have 2 tables (PEG and SEG) that have a tier with an associated range for each. I am trying to take the Category column and the Value column to return the correct Tier (Expected Result column) based upon their combination. I prepopulated the Expected Result column based upon what the result should be. If have tried using an IF function combined with AND as well as OR and none of the multitude of formulas I have tried are returning the correct results. I am sure that I am missing something simple, but am in need of assistance with this. Any help would be tremendously appreciated. Thank you.

 

[jasonb75]

Try this in C2 and fill down

=IF(B2="PEG",LOOKUP(A2,$F$4:$F$7,$E$4:$E$7),IF(B2="SEG",LOOKUP(A2,$F$14:$F$17,$E$14:$E$17),B2))

edit:- thanks for the mini-sheet @Toadstool, I don't have the patience to retype screen caps.

Book1
	A	B	C	D	E	F	G
1	Value	Category	Result
2	150	PEG	P1			Peg
3	174	PEG	P1		Tier	Bottom Range	Top Range
4	94	SEG	S1		P1	0	250
5	590	SEG	S3		P2	250	1000
6	73	PPS	PPS		P3	1000	99999
7	25	PPP	PPP		P4
8	127	SEG	S2		PPP
9	312	PEG	P2		TRM
10	833	PPS	PPS
11	10	TRM	TRM
12	575	TRM	TRM			Seg
13	276	SEG	S2		Tier	Bottom Range	Top Range
14	705	SEG	S3		S1	0	100
15	259	SEG	S2		S2	100	500
16	50	PEG	P1		S3	500	99999
17	452	SEG	S2		S4
18	317	PEG	P2		PPS
Sheet7

Cell Formulas
Range	Formula
C2:C18	C2	=IF(B2="PEG",LOOKUP(A2,$F$4:$F$7,$E$4:$E$7),IF(B2="SEG",LOOKUP(A2,$F$14:$F$17,$E$14:$E$17),B2))

 

[Toadstool]

Hi Ketterj,

If the range has a bottom limit then I've added a top limit (otherwise it gets more complicated) but this should do what you ask.

Ketterj.xlsx
	A	B	C	D	E	F	G
1	Value	Category	Result
2	150	PEG	P1			Peg
3	174	PEG	P1		Tier	Bottom Range	Top Range
4	94	SEG	S1		P1	0	250
5	590	SEG	S3		P2	250	1000
6	73	PPS	PPS		P3	1000	99999
7	25	PPP	PPP		P4
8	127	SEG	S2		PPP
9	312	PEG	P2		TRM
10	833	PPS	PPS
11	10	TRM	TRM
12	575	TRM	TRM			Seg
13	276	SEG	S2		Tier	Bottom Range	Top Range
14	705	SEG	S3		S1	0	100
15	259	SEG	S2		S2	100	500
16	50	PEG	P1		S3	500	99999
17	452	SEG	S2		S4
18	317	PEG	P2		PPS
Sheet1

Cell Formulas
Range	Formula
C2:C18	C2	=IF(A2="","",IF(ISNUMBER(MATCH(B2,$E$3:$E$18,0)),B2,IF(B2=$F$2,INDEX($E$4:$E$9,AGGREGATE(15,6,ROW($F$4:$F$9)-ROW($F$3)/(($F$4:$F$9<=A2)*($G$4:$G$9>=A2)),1)),INDEX($E$14:$E$19,AGGREGATE(15,6,ROW($F$14:$F$19)-ROW($F$13)/(($F$14:$F$19<=A2)*($G$14:$G$19>=A2)),1)))))