Nested If w/ VLOOKUP & Ranges

[mhasham]

Hello Folks,

Im looking to create a group based of the following categories with a specific set of range rules.

E= IF column (e) is >5
and column (c) <5
and SUM column (es1+es2) <5
else BLANK

C= IF column (c) is >5
and column (e) <5
and SUM column (es1+es2) <5
else BLANK

EC= IF column (c) is >5
and column (e) >5
and SUM column (es1+es2) <5
else BLANK

ES= IF SUM column (es1+es2) >5
else BLANK

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]User[/TD]
[TD]electronics (e)[/TD]
[TD]cats (c)[/TD]
[TD]special 1 (es)[/TD]
[TD]special 2 (es)[/TD]
[/TR]
[TR]
[TD]Brett Hart[/TD]
[TD]176[/TD]
[TD]66[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Charlie Murphy[/TD]
[TD]34[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Sam Pelt[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]53[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]Max Keller[/TD]
[TD]3[/TD]
[TD]45[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


SOLUTION that I'm trying to strive for is listed below. This would be on a separate worksheet and where the formula would be placed. This would be reading off of the data worksheet. I am thinking it probably requires a Nested IF statement in combination with a VLOOKUP for the User names and the ability to read column headers or column numbers or something to that nature.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]User[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]Brett Hart[/TD]
[TD]EC[/TD]
[/TR]
[TR]
[TD]Charlie Murphy[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Sam Pelt[/TD]
[TD]ES[/TD]
[/TR]
[TR]
[TD]Max Keller[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be much appreciated

Regards Mo.

 

[RasGhul]

Hi mhasham,

This works on my sheet the trick will be getting the sheet references right between sheets. I recommend getting this working on the same sheet first, then we amend to suit across sheets after.

If you let me know what you're sheets are called and the specific range of your data I could try to adjust for you;

Book1
	A	B	C	D	E
1	User	electronics (e)	cats (c)	special 1 (es)	special 2 (es)
2	Brett Hart	176	66	0	0
3	Charlie Murphy	34	4	0	0
4	Sam Pelt	6	0	53	53
5	Max Keller	3	45	0	0
6
7	User	GROUP
8	Brett Hart	EC
9	Charlie Murphy	E
10	Sam Pelt	ES
11	Max Keller	C
Sheet1

Cell Formulas
Range	Formula
B8	=IF(SUM((VLOOKUP(A8,$A$2:$E$5,4,0)+(VLOOKUP(A8,$A$2:$E$5,5,0))))>5,"ES",IF(AND(VLOOKUP(A8,$A$2:$E$5,2,0)<5,VLOOKUP(A8,$A$2:$E$5,3,0)>5,SUM((VLOOKUP(A8,$A$2:$E$5,4,0)+(VLOOKUP(A8,$A$2:$E$5,5,0))))<5),"C",IF(AND(VLOOKUP(A8,$A$2:$E$5,2,0)>5,VLOOKUP(A8,$A$2:$E$5,3,0)<5,SUM((VLOOKUP(A8,$A$2:$E$5,4,0)+(VLOOKUP(A8,$A$2:$E$5,5,0))))<5),"E",IF(AND(VLOOKUP(A8,$A$2:$E$5,2,0)>5,VLOOKUP(A8,$A$2:$E$5,3,0)>5,SUM((VLOOKUP(A8,$A$2:$E$5,4,0)+(VLOOKUP(A8,$A$2:$E$5,5,0))))<5),"EC",""))))

 

[mhasham]

Sorry for the late reply. This worked wonders!!!!
I changed the hard coded values into a percentage threshold.

Thanks again.