Tag Archives: LibreOffice

Retrieving Values from Another Column in LibreOffice Calc


Environment

  • LibreOffice 6.0.1.1

Methods

This article explains two methods for retrieving values from another column in LibreOffice Calc. One method focuses on getting values from cells to the right of the current cell, while the other allows you to retrieve values from both left and right columns. The latter method is more complex and involves longer formulas. We’ll discuss the commonly used approaches for each.

Example

Let’s consider a table as shown below. We have a table below, and we want to display its values in the table above. This is a simplified example for demonstration purposes.

A B C
1 Name Age Volume
2 ABC
3 DEF
4 GHI
5
6 Volume Name Age
7 100 ABC 10
8 200 DEF 20
9 300 GHI 20

We want the final result to look like this:

A B C
1 Name Age Volume
2 ABC 10 100
3 DEF 20 200
4 GHI 30 300
5
6 Volume Name Age
7 100 ABC 10
8 200 DEF 20
9 300 GHI 20

Retrieving Values from Cells to the Right

Let’s consider how to retrieve and display values from the “Age” column.

We’ll use the VLOOKUP function.

In the cell to the right of “ABC,” enter the following formula:

Explanation of Parameters

A2 specifies the value to be looked up. Here, it references cell A2, so it will be “ABC.”

$B$7:$C$9 represents the portion of the table containing the data. We use absolute references to ensure it doesn’t change when copied. Specifically, it covers the following area:

ABC 10
DEF 20
GHI 20

2 indicates the “2nd column” of the table data.

The final 0 (or False) is used when the table data is not sorted based on the search key (here, “ABC”). If there’s no match, it will display an error. If the data is sorted and the value is guaranteed to exist, you can use 1 or True instead. Using 1 with unsorted data may display unexpected values for unmatched items.

This way, you can display values from the “Age” column.

Retrieving Values from Cells to the Left and Right

Let’s consider how to retrieve and display values for both “Age” and “Volume.” For this example, we’ll focus on retrieving “Volume” values.

There are methods using OFFSET and MATCH as well as LOOKUP.

If you want to use OFFSET and MATCH, enter the following formula in the cell two cells to the right of “ABC”:

MATCH

MATCH returns the position where a value is found. It was used as MATCH(A2, $B$7:$B$9, 0) in this example.

A2 is the value you want to search for.

$B$7:$B$9 is the search range. We use absolute references because the search range doesn’t change based on rows. Specifically, it covers the following area:

ABC
DEF
GHI

0 indicates an exact match.

When you search for the first value (in this case, “ABC”), it returns 1.

OFFSET

OFFSET returns the value of a cell that is a specified number of rows and columns from a base cell. In this example, it was used as OFFSET($A$7, MATCH(A2, $B$7:$B$9, 0) - 1, 0).

$B$7 is the base cell. We use absolute references because we want the top cell in the column where the desired value is contained to be the base cell.

MATCH(A2, $B$7:$B$9, 0) - 1 determines how many rows away from the base cell to fetch. MATCH returns 1 for the first cell, so we subtract 1.

0 indicates how many columns away from the base cell to fetch. Since the base cell is in the desired column, there’s no horizontal offset, so it’s 0.

You can use negative values for row and column offsets as well, allowing you to fetch values in any direction (left, right, up, or down).

Using OFFSET allows you to retrieve values from both the right and left columns.

If you want to use LOOKUP, enter the following formula in the cell two cells to the right of “ABC”:

LOOKUP

LOOKUP retrieves values from a row or column based on a specified value.

A2 is the cell containing the value you want to look up.

$B$7:$B$9 is the lookup range. We use absolute references because this range doesn’t change.

$A$7:$A$9 is the range where the values to return are located. We use absolute references because this range doesn’t change.

LOOKUP is suitable when the values in the lookup range are sorted. However, if there’s no match, it will return the value from the last cell. Therefore, it’s recommended to use LOOKUP only when you are certain that the values in the lookup range are sorted and that the value you want to look up will always exist.


LibreOffice: Show Database Data on Spread Sheet


I will introduce the way to show database data on spread sheet of LibreOffice Calc, using LibreOffice Base. The data you retrieve from the database onto the spread sheet can be used for any table and pivot tables. I wrote the article “Excel: Retrieve and Show Database Data” before, which shows the way to get the data onto Excel through ODBC, but LibreOffice enables us to do the same thing for free.

Continue reading LibreOffice: Show Database Data on Spread Sheet

(日本語) LibreOffice Calc: Basic でセルをまとめて扱う


Sorry, this entry is only available in Japanese.


(日本語) LibreOffice Basic: サブプロシージャ・関数


Sorry, this entry is only available in Japanese.


(日本語) LibreOffice Basic on Calc: 再描画を停止する方法


Sorry, this entry is only available in Japanese.