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:
|
1 |
=VLOOKUP(A2, $B$7:$C$9, 2, 0) |
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”:
|
1 |
=OFFSET($A$7, MATCH(A2, $B$7:$B$9, 0) - 1, 0) |
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”:
|
1 |
=LOOKUP(A2, $B$7:$B$9, $A$7:$A$9, 0) |
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.
