issues with If and vlookup - adding up by 1 point -

topazfae

I had a spreadsheet of results of 5, 4+, 4, 3+, 3, 2+, 2, 1+, 1, and 0, and each of those numbers has a point value in another spreadsheet. For some reason, it would count twice. If I look up 1, the one value will give me an extra point (I assume because of 1+). How do I ensure they don't do that? The following formula I have is -

=IF([Weighted Point]@row = "", VLOOKUP([Scale Earned]@row, {Scale Table}, 2), IF([Weighted Point]@row = 0.5, VLOOKUP([Scale Earned]@row, {Scale Table}, 3), IF([Weighted Point]@row = 1, VLOOKUP([Scale Earned]@row, {Scale Table}, 4))))

The Scale table on the other spreadsheet - there are four columns with 5, 4+, 4, etc

1st column is the scale point earned, 2nd column is the regular point without the weighted point of 0.5 or 1.0, the 3rd column, with 0.5, and 4th column with 1.0.

So, when I use the formula

My 5 shows up as 4, which is correct, 4+ shows up as 3.40, which is correct, but my 4 shows up as 4.0, which is incorrect. In my vlookup table, it's 3.0. Why am I getting one point extra for my 4. Those numbers without + have one point more to their values.

Anything would be helpful.

Thanks,

topazfae

I solved it - you have to add false at the end of each vlookup, ensuring it is an exact match.

The solved formula is - =IF([Weighted Point]@row = "", VLOOKUP([Scale Earned]@row, {Scale Table}, 2, FALSE), IF([Weighted Point]@row = 0.5, VLOOKUP([Scale Earned]@row, {Scale Table}, 3, FALSE), IF([Weighted Point]@row = 1, VLOOKUP([Scale Earned]@row, {Scale Table}, 4, FALSE))))