Calc Guide

The return_column_index identifies the column to return; a value of 1
returns the first column in the range. The statement =VLOOKUP("Bob";
A1:G9; 1) finds the first row in A1:G9 containing the text Bob, and
returns the value in the first column. The first column is the searched
column, so the text Bob is returned. If the column index is 2, then the
value in the cell to the right of Bob is returned: column B.
The final column, sort_order, is optional. The default value for
sort_order is 1, which specifies that the first column is sorted in
ascending order; a value of 0 specifies that the data is not sorted. A
non-sorted list is searched by sequentially checking every cell in the
first column for an exact match. If an exact match is not found, the text
#N/A is returned.
A more efficient search routine is used if the data is sorted in
ascending order. If one exact match exists, the returned value is the
same as for a non-sorted list; but it is faster. If a match does not exist,
the largest value in the column that is less than or equal to the search
value is returned. For example, searching for 7 in (3, 5, 10) returns 5
because 7 is between 5 and 10. Searching for 27 returns 10, and
searching for 2 returns #N/A because there is no match and no value
less than 2.
Use VLOOKUP when:
• The data is arranged in rows and you want to return data from
the same row. For example, student names with test and quiz
scores to the right of the student’s name.
• Searching the first column of a range of data.
Search a block of data using HLOOKUP
Use HLOOKUP to search the first row (rows are horizontal) of a block
of data and return the value from a row in the same column. HLOOKUP
supports the same form and arguments as VLOOKUP:
HLOOKUP(search_value; search_range; return_row_index)
HLOOKUP(search_value; search_range; return_row_index; sort_order)
Use HLOOKUP when:
• The data is arranged in columns and you want to return data from
the same column. For example, student names with test and quiz
scores underneath the student’s name.
• Searching the first row of a range of data.
380 OpenOffice.org 3.x Calc Guide