Follow Techotopia on Twitter

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
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions




OpenOffice Calc 3.x Guide
Previous Page Home Next Page


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.

1 Score Grade
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