LinuxQuestions.org - Calc Spreadsheet VLOOKUP Question

- Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)

- - Calc Spreadsheet VLOOKUP Question (https://www.linuxquestions.org/questions/linux-software-2/calc-spreadsheet-vlookup-question-605367/)

Calc Spreadsheet VLOOKUP Question

Hi. I'm using OpenOffice.org 2.3 on Ubuntu 7.10. I've got a question about VLOOKUP in Calc. I'm trying to get it to work with letter grades (I'm calculating my GPA), but I've run into trouble. Let's say I have two tables:

Table 1:

Code:

EBUS311 3 A+ !!!

Table 2:

Code:

A+    4.33

A      4.00

A-    3.67

B+    3.33

B      3.00

B-    2.67

In the first table, the columns are: Course, Credits, Grade, and Quality Points. The "!!!" in the Quality Points cell represents what I'm trying to create the VLOOKUP for. In the second table I basically have the numerical equivalents for the letter grades. The idea is I input the letter grades in the first table, and calculations are done (for the quality points) using the number equivalent in the second table.

Basically, I want to VLOOKUP A3 (from the first table) in the second table, and get the value of the second cell, so that I can multiply it by A2 (first table) to get the quality points (quality points is equal to grade times credits). I'm using something like this in A4 (!!!):

Code:

=VLOOKUP(A3;Table2.A1:B6;2;FALSE)*A2

The problem seems to be with the plus sign (+) or something (I think). The VLOOKUP returns the value of "A", which is 4.00, instead of the value of "A+" (4.33). If I use an "A-" it works fine, returning a 3.67.

What is going on here? It's been a while since I've used a spreadsheet so I'm pretty rusty. I know I could get this to work by using percentages, but I'd really like to stick to letter grades. Any help would be greatly appreciated.

I think you have found a bug in OO 2.3 calc

Quote:

A+ 4.33 2 "=MATCH(A8;$A$1:$A$6;0)
A 4 2 "=MATCH(A9;$A$1:$A$6;0)
A- 3.67 3 "=MATCH(A10;$A$1:$A$6;0)
B+ 3.33 5 "=MATCH(A11;$A$1:$A$6;0)
B 3 5 "=MATCH(A12;$A$1:$A$6;0)
B- 2.67 6 "=MATCH(A13;$A$1:$A$6;0)

A+
A
A-
B+
B
B-

I was looking at the MATCH function as a possibility, but found this.
Note the result of the MATCH function using the formula shown afterward.
The + sign causes a glitch.

And it does work for text strings.

Quote:

AA 4.33 1 "=MATCH(A8;$A$1:$A$6;0)
AB 4 2 "=MATCH(A9;$A$1:$A$6;0)
AC 3.67 3 "=MATCH(A10;$A$1:$A$6;0)
BA 3.33 4 "=MATCH(A11;$A$1:$A$6;0)
BB 3 5 "=MATCH(A12;$A$1:$A$6;0)
BC 2.67 6 "=MATCH(A13;$A$1:$A$6;0)

AA
AB
AC
BA
BB
BC

Hi allend. Thanks man, I really appreciate you taking the time to test this. I think I might have found what is triggering this behavior. I went to Tools > Options > OpenOffice.org Calc > Calculate and I removed the checkmark from the "Enable regular expressions in formulas" box. Doing so makes the VLOOKUP work as expected. Can you (or anyone else) confirm this?

Hi win32sux. Yes, removing the checkmark that you describe does make the MATCH function work as expected. Thanks for the tip!

I feel that this post is better of in Linux-Software since it's not about
OO under windows, and have moved it accordingly. :}

Cheers,
Tink