VLOOKUP in LibreOffice Calc

VLOOKUP is a powerful function to search for data in tables and return the corresponding information from another column in LibreOffice Calc. It searches the first column of a data block for a specific value and returns the value of a specified cell in the same row from a different column.

But there are some important rules.

How to use VLOOKUP

VLOOKUP needs a search value, the range to search in, the column index to return, and an optional argument for sort order.

The VLOOKUP(search_value; search_range; return_column_index; sort_order) form specifies four arguments:

1. search_value is the value you want to look for.
2. search_range is the range of cells in which the value is searched.
3. return_column_index indicates which column of the range from which the value is returned.
4. sort_order (optional) allows the data to be searched exactly when FALSE or zero is entered.

For example, =VLOOKUP(B2; $F$2:$G$10; 2; 0) finds the value in B2 in the first column of F2:G10 and returns the matching entry from column G.

Exact match, approximate match, and why #N/A appears

The default option for VLOOKUP is searching for an approximate match.

Here, the data in the first column of search_range must be sorted in ascending order. The search makes the best match, so it returns a value where the search key is greater than or equal to the value in the lookup column.

With an exact match, VLOOKUP seeks the precise value. The data does not need to be sorted, but if a precise match cannot be found, the error #N/A appears.

When INDEX and MATCH are the safer option

Experienced spreadsheet users often prefer INDEX and MATCH to VLOOKUP. Unlike VLOOKUP's check against the first column only, INDEX and MATCH isolate the row you want, then return a cell from that row. They are more flexible when the search column's position shifts, or the table reorders. Use INDEX to return a cell reference; MATCH finds the relative position of the search value in a range.

INDEX is followed by a range, the row number and the column number within that range, as INDEX(table; row_num; column_num).

MATCH then finds the position of the search criterion within the range you set, as MATCH(lookup; lookup_array; match_type). For an exact match, use 0 as the third parameter.

Put them together as =INDEX($F$2:$G$10; MATCH(B2; $F$2:$F$10; 0); 2).

What Calc now offers with XLOOKUP

LibreOffice Calc 24.8 and later support XLOOKUP for increased flexibility. It can make exact or approximate matches, it supports wildcards for partial matches, and it can search vertically and horizontally in a single function.

The XLOOKUP function has a simpler syntax XLOOKUP(lookup_value; lookup_array; return_array), and can even replace LOOKUP or HLOOKUP.

Use the appropriate search type, considering whether your data is sorted, whether column positions might change, and the version of Calc you have. VLOOKUP is quick and effective, but when flexibility is needed, INDEX and MATCH ensure more accurate results. More recent Calc software comes with advanced options in XLOOKUP.