Lookup Returning Incorrect data

[ilomeli]

I'm using the lookup function to grade employees based on their performance. The OT column (M) seems to work and returns the correct score for the employee below. But the rest of the columns (ROP, QA and Grade) return incorrect scores. Below are also the grades that should be returned based on an employees performance in each category. This is all on one sheet. The formula I used is for column M was: =LOOKUP(I4,$J$51:$K$54) this is the section that worked. The formula used for ROP is =LOOKUP(H4,$D$51:$E$54) This and the formula for final grade are the ones that keep returning the incorrect score and data. Maybe theres a better formula to use?



[TABLE="width: 460"]
<colgroup><col width="66" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2332;"> <col width="41" style="width: 31pt; mso-width-source: userset; mso-width-alt: 1450;"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2133;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2161;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2161;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1962;"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1706;"> <col width="66" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2332;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1848;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1848;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1848;"> <tbody>[TR]
[TD="class: xl78, width: 107, bgcolor: #D9D9D9, colspan: 2"]ROP[/TD]
[TD="class: xl72, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl78, width: 122, bgcolor: #D9D9D9, colspan: 2"]QA[/TD]
[TD="class: xl72, width: 55, bgcolor: transparent"][/TD]
[TD="class: xl78, width: 114, bgcolor: #D9D9D9, colspan: 2"]OT[/TD]
[TD="class: xl72, width: 52, bgcolor: transparent"][/TD]
[TD="class: xl72, width: 52, bgcolor: transparent"][/TD]
[TD="class: xl72, width: 52, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: #D0CECE"]Score[/TD]
[TD="class: xl74, bgcolor: #D0CECE"]Grade[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: #D0CECE"]Score[/TD]
[TD="class: xl74, bgcolor: #D0CECE"]Grade[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: #D0CECE"]Score[/TD]
[TD="class: xl74, bgcolor: #D0CECE"]Grade[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: #D9D9D9, colspan: 2"]Grade[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent, align: right"]300[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"]4[/TD]
[TD="class: xl73, bgcolor: transparent"]A+[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent, align: right"]260[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]0.95[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent, align: right"]0.14[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"]3[/TD]
[TD="class: xl73, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent, align: right"]249[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]0.91[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent, align: right"]0.26[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"]2[/TD]
[TD="class: xl73, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent, align: right"]229[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]0.9[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent, align: right"]0.39[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"]1[/TD]
[TD="class: xl73, bgcolor: transparent"]C[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 596"]
<colgroup><col width="86" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3043;"> <col width="66" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2332;"> <col width="41" style="width: 31pt; mso-width-source: userset; mso-width-alt: 1450;"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2133;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2161;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2161;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1962;"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1706;"> <col width="66" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2332;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1848;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1848;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1848;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1848;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1564;"> <tbody>[TR]
[TD="class: xl82, width: 86, bgcolor: #E7E6E6"]C[/TD]
[TD="class: xl82, width: 66, bgcolor: #E7E6E6"]D[/TD]
[TD="class: xl82, width: 41, bgcolor: #E7E6E6"]E[/TD]
[TD="class: xl82, width: 60, bgcolor: #E7E6E6"]F[/TD]
[TD="class: xl82, width: 61, bgcolor: #E7E6E6"]G[/TD]
[TD="class: xl82, width: 61, bgcolor: #E7E6E6"]H[/TD]
[TD="class: xl82, width: 55, bgcolor: #E7E6E6"]I[/TD]
[TD="class: xl82, width: 48, bgcolor: #E7E6E6"]J[/TD]
[TD="class: xl82, width: 66, bgcolor: #E7E6E6"]K[/TD]
[TD="class: xl83, width: 52, bgcolor: #E7E6E6"]L[/TD]
[TD="class: xl83, width: 52, bgcolor: #E7E6E6"]M[/TD]
[TD="class: xl83, width: 52, bgcolor: #E7E6E6"]N[/TD]
[TD="class: xl83, width: 52, bgcolor: #E7E6E6"]O[/TD]
[TD="class: xl83, width: 44, bgcolor: #E7E6E6"]P[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #E7E6E6"]Name[/TD]
[TD="class: xl79, bgcolor: #E7E6E6"]Comps[/TD]
[TD="class: xl79, bgcolor: #E7E6E6"]GSR[/TD]
[TD="class: xl79, bgcolor: #E7E6E6"]Diff[/TD]
[TD="class: xl79, bgcolor: #E7E6E6"]PUA[/TD]
[TD="class: xl79, bgcolor: #E7E6E6"]ROP[/TD]
[TD="class: xl79, bgcolor: #E7E6E6"]OT %[/TD]
[TD="class: xl79, bgcolor: #E7E6E6"]Late[/TD]
[TD="class: xl79, bgcolor: #E7E6E6"]QA[/TD]
[TD="class: xl81, bgcolor: #E7E6E6"]ROP[/TD]
[TD="class: xl81, bgcolor: #E7E6E6"]OT[/TD]
[TD="class: xl81, bgcolor: #E7E6E6"]QA[/TD]
[TD="class: xl81, bgcolor: #E7E6E6"]AVG[/TD]
[TD="class: xl81, bgcolor: #E7E6E6"]Grade[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]Israel[/TD]
[TD="class: xl73, bgcolor: transparent"]23[/TD]
[TD="class: xl73, bgcolor: transparent"]1[/TD]
[TD="class: xl74"]($4,363)[/TD]
[TD="class: xl74, bgcolor: transparent"]$1,444 [/TD]
[TD="class: xl78, bgcolor: transparent"]$352[/TD]
[TD="class: xl75, bgcolor: transparent"]21%[/TD]
[TD="class: xl73, bgcolor: transparent"]1[/TD]
[TD="class: xl75, bgcolor: transparent"]100%[/TD]
[TD="class: xl72, bgcolor: transparent"]1[/TD]
[TD="class: xl72, bgcolor: transparent"]3[/TD]
[TD="class: xl72, bgcolor: transparent"]1[/TD]
[TD="class: xl84, bgcolor: transparent"]1.6667[/TD]
[TD="class: xl77, bgcolor: transparent"]#N/A
[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl84, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[NickRed18]

Good Morning ilomeli,

I'm pretty sure the LOOKUP function can only handle 1 row/1 column at a time. You're looking up using the ranges J51:K54 (4 rows, 2 columns), and D51:E54 (again 4 rows and 2 columns). It may be a coincidence the OT columns works fine. I'd say move all your lookup fields into 1 column or 1 row instead of a 4x2. If that is not possible for you, then post some more of your worksheet and we'll try something else!

- Nick

 

[steve the fish]

Hi. You need to sort your lists lowest to highest. Also make sure the dollar sign is just formatting and so the number to be looked up is still a number.

 

[ilomeli]

Hi. You need to sort your lists lowest to highest. Also make sure the dollar sign is just formatting and so the number to be looked up is still a number.

Steve the fish,

Thanks a lot! it was as simple as sorting the scores lowest to highest as you said.