                On-line Guides All Guides eBook Store iOS / Android Linux for Beginners Office Productivity Linux Installation Linux Security Linux Utilities Linux Virtualization Linux Kernel System/Network Admin Programming Scripting Languages Development Tools Web Development GUI Toolkits/Desktop Databases Mail Systems openSolaris Eclipse Documentation Techotopia.com Virtuatopia.com Answertopia.com How To Guides Virtualization General System Admin Linux Security Linux Filesystems Web Servers Graphics & Desktop PC Hardware Windows Problem Solutions Privacy Policy  OpenOffice Calc 3.x Guide
Previous Page Home Next Page

## Examples

Consider the data in Table 1. Each student’s information is stored in a single row. Write a formula to return the average grade for Fred. The problem can be restated as Search column A in the range A1:G16 for Fred and return the value in column F (column F is the sixth column). The obvious solution is =VLOOKUP("Fred"; A2:G16; 6). Equally obvious is =LOOKUP("Fred"; A2:A16; F2:F16).

It is common for the first row in a range to contains column headers. All of the search functions check the first row to see if there is a match and then ignore it if it does not contain a match, in case the first row is a header.

What if the column heading Average is known, but not the column containing the average? Find the column containing Average rather than hard coding the value 6. A slight modification using MATCH to find the column yields =VLOOKUP("Fred"; A2:G16; MATCH("Average"; A1:G1; 0)); notice that the heading is not sorted. As an exercise, use HLOOKUP to find Average and then MATCH to find the row containing Fred.

As a final example, write a formula to assign grades based on a student’s average score. Assume that a score less than 51 is an F, less than 61 is an E, less than 71 is a D, less than 81 is a C, less than 91 is a B, and 91 to 100 is an A. Assume that the values in Table 9 are in Sheet2.

Table 9. Associate scores to a grade.

A B
2 0 F
3 51 E
4 61 D
5 71 C
6 81 B
7 91 A

The formula =VLOOKUP(83; \$Sheet2.\$A\$2:\$B\$7; 2) is an obvious solution. Dollar signs are used so that the formula can be copied and pasted to a different location and it will still reference the same values in Table 9.

OpenOffice Calc 3.x Guide
Previous Page Home Next Page Published under the terms of the Creative Commons License Design by Interspire