Calc Guide

Search a row or column using LOOKUP
LOOKUP is similar to HLOOKUP and VLOOKUP. The search range for
the LOOKUP function is a single sorted row or column. LOOKUP has
two forms:
LOOKUP(search_value; search_range)
LOOKUP(search_value; search_range; return_range)
The search value is the same as HLOOKUP and VLOOKUP. The search
range, however, must be a single row or a single column; for example,
A7:A12 (values in column A) or C5:Q5 (values in row 5). If the
return_range is omitted, the matched value is returned. Using
LOOKUP without a return range is the same as using HLOOKUP or
VLOOKUP with a column index of 1.
The return range must be a single row or column containing the same
number of elements as the search range. If the search value is found in
the fourth cell in the search range, then the value in the fourth cell in
the return range is returned. The return range can have a different
orientation than the search range. In other words, the search range
can be a row and the return range may be a column.
Use LOOKUP when:
• The search data is sorted in ascending order.
• The search data is not stored in the same row, column, or
orientation as the return data.
Use MATCH to find the index of a value in a range
Use MATCH to search a single row or column and return the position
that matches the search value. Use MATCH to find the index of a value
in a range. The supported forms for MATCH are as follows:
=MATCH(search_value; search_range)
=MATCH(search_value; search_range; search_type)
The search value and search range are the same as for LOOKUP. The
final argument, search type, controls how the search is performed. A
search type of 1, sorted in ascending order, is the default. A search
type of -1 indicates that the list is sorted in descending order. A search
type of 0 indicates that the list is not sorted. Regular expressions can
only be used on an unsorted list.
Use MATCH when:
• You need an index into the range rather than the value.
Chapter 13 Calc as a Simple Database 381