Using Vlookup in OpenOffice.org Calc - How to Use the VLookup Function in OpenOffice Calc

Vlookup is one of the most useful, but least understood, functions in OpenOffice Calc (and Microsoft Excel). This tutorial will show you how to use it effectively.

OpenOffice.org Calc is an incredibly useful tool whether it's used at home or in the office, and it makes an excellent replacement for the very popular (but relatively expensive) Microsoft Excel; both of these applications share many useful functions, and one of these functions is also one of the most useful, but least understood, functions - vlookup.

Using Columns of Data in OpenOffice Calc

Data in Calc (like any spreadsheet) is normally stored in columns, something like:

__|_____A_____|B|

1_|Gas________|_$100|

2_|Food(Home)|$500|

3_|Meals_______|_$100|

It's then possible for a user to use formulae to analyse the data in those columns:

4_|Total________|=SUM(B1:B3)

And then the user can see the results of that formula:

4_|Total________|_$700|

However, they can also make use of the data in other ways, for example, by using vlookup.

Using Vlookup to Find Data in a Calc Spreadsheet

The concept of vlookup is quite simple:

Step 1: the function searches the first column of a range of data for a set string
Step 2: it then returns the contents of the cell that is a set number of columns to the right of the data found in step 1

It, therefore, needs three pieces of information:

• the data to be searched for
• the range of cells containing the data
• the offset (i.e. the number of the columns to the right of the search column)

So if, for example, the data to be analysed is stored in the range H1 to J5:

|_____H|I|J|

1_|name____|age|gas

2_|Bill______|_45|500

3_|Fred_____|_21|750

4_|Jane_____|_23|100

5_|Mary_____|_56|257

then to look up the gas costs for Fred the following formula would be used:

=VLOOKUP("Fred";H2:J5;3)

and that would return the result 750.

The advantage of Using Vlookup

A key advantage of using vlookup is that it allows the user to look up different information very easily; for example the following will display Fred's details:

|A|B|

1_|Name|Fred|

2_|Age_|=VLOOKUP(B1;H2:J5;2)

3_|Gas_|=VLOOKUP(B1;H2:J5;3)

and so if the contents of B1 are changed to 'Jane' then her details will be displayed instead of Fred's.

Optimizing Vlookup Usage

Although vlookup is very useful there are some things that can cause problems for the unwary user. The first consideration is one of speed - a sorted index is faster to search than an unsorted one, and so, where ever possible, the index should be sorted in ascending order.

The second issue is much more of a problem: vlookup will always give an answer - even if it's the wrong one. For example, take:

=VLOOKUP("John";H2:J5;3)

Even though there is no 'John' in the data this will still return the result 750 (the actual result for 'Jane'); and:

=VLOOKUP("Zebedee";H2:J5;3)

will return 257 (the expected result for 'Mary'). The solution is to input a fourth variable and set this to 'False; for example:

=VLOOKUP("Zebedee";H2:J5;3;FALSE)

This time the result will be #N/A whenever a record cannot be found.

Conclusion

Although there are a couple of technicalities that can trip up a user, if these are taken account of, then vlookup is a very useful and versatile function that can make data manipulation much easier in OpenOffice Calc.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---