vlookup() function in OpenOffice Calc with an example

The vlookup or Vertical Look Up Function in Openoffice calc can be used to search a range of cells.Suppose you have two spreadsheets and you want to append data from one sheet to another. We can use vlookup() in open office calc to accomplish this task. vlookup() searches for values in one column and given values in another column in true condition. Read on to know how to user VLookup.

The syntax for vlookup() is
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

1. lookup_value - This is the value that we use for searching.
2. table_array,col_index_num - This is a reference to the sheet and two or more columns of data where we are searching the value.
3. Col_index_num The column number in the sheet from which the matching value must be returned.
4. Range_lookup - True or false condition.
   If we apply true , an exact or approximate match is returned. If it is not found, the next largest value which is less than the lookup_value is returned. If we set false or '0' as range lookup, the vlookup function will search for only the exact match.

Let us check with an example.
Consider two sheets. One sheet with an email field and another sheet with email field and phone numbers. Our target is to append phone numbers in the first sheet if the email matches in the two sheets. The two sheets contains 100 rows each.
So we write the vlookup function in B1 as
=VLOOKUP(A1,Sheet2.$A$1:B100,2,0)
Clicking on the right end of the B1 will append all the values with matching email.

• A1 refers the email values in A1 cell.
• Sheet2.$A$1:B100 defines the reference to second sheet and the email field and phone number filed where you have to check values.
• 2 refers the no of column counting from email field.
• 0 is the false condition, which will return exact match if exists.

Please note that the email field should be unique. Otherwise it will return values of the first exact match.