Question Regarding Lookups Against Ranges

[Jezzzza]

Hi,

I am trying to pull Project Manager rankings/grades based on a pivot table field (average variance of planned hours vs worked hours) and I need it to grade based on number not +/-. For example this is the scale:

So anything from 0 being no variance to +/-5% variance would be an A, and so forth

This is the formula I am using in a column adjacent to the pivot table:

=IF(ISBLANK(B7)=TRUE,"",VLOOKUP(B7,'Rank Lookup'!$A$2:$B$12,2))


But as you can see, the highlighted, it is not pulling the correct grade.

Any help is appreciated!

 

[Jeffrey Mahoney]

Please try this:

Book2
	B	C
7	4%	A
Sheet3

Cell Formulas
Range	Formula
C7	C7	=IF(B7="","",VLOOKUP(ABS(B7),'Rank Lookup'!$A$2:$B$7,2,TRUE))

Book2
	A	B
1	Variance	Score
2	0	A
3	5%	B
4	10%	C
5	15%	D
6	20%	E
7	80%	F
Rank Lookup