Vertical lookup with VLOOKUP and XLOOKUP

With a vertical lookup you fetch a value from another table, such as the price for a product code. In Excel the function is called VLOOKUP, and the newer one XLOOKUP. LibreOffice Calc and Euro-Office both have them, with the same names.

VLOOKUP

=VLOOKUP(lookup_value, table, column_number, 0)

• lookup_value: what you are looking for, for example the product code in A2;
• table: the range you search in. The lookup value must be in the first column;
• column_number: the column of the table you want the answer from (1 is the first);
• 0: exact match only. If you leave this out, the function assumes the table is sorted and may return the wrong value.

Example: the price (column 3) for the code in A2, from the Products sheet:

The only difference is how the sheet is written (a full stop or an exclamation mark). If you simply click the other sheet while typing and select the range, the program writes it correctly for you. Press F4 to add the dollar signs, so the range stays fixed when you drag the formula down.

XLOOKUP: look up to the left and with your own message

=XLOOKUP(lookup_value, lookup_column, result_column, "not found")

XLOOKUP does not need the lookup value to be in the first column, uses an exact match by default and can show your own text if nothing is found. In LibreOffice, XLOOKUP is available in recent versions (from 24.8).

Example in Calc: =XLOOKUP(A2,$Products.$A$2:$A$50,$Products.$C$2:$C$50,"unknown code")

Getting #N/A?

• The value really isn't there, or it has a space after it. Use TRIM or check the table.
• Number versus text: "1001" as text is not the same as 1001 as a number.
• With VLOOKUP: the lookup value is not in the first column of the range. Use XLOOKUP instead.

In Euro-Office

The formulas are the same as in Excel. You'll find explanations and examples in Formulas and functions: SUM, AVERAGE, IF and VLOOKUP, VLOOKUP returns #N/A: how to fix it (fixing #N/A) and XLOOKUP: look up to the left and with your own message (XLOOKUP).