Search code examples
libreofficelibreoffice-calc

Add columns in result of if

I have two sheets. The first contains 4 columns and the second contains a column with cells that each can take values from the first column of the first sheet.

So e.g. Sheet 1:

    A  B  C  D
1   A1 B1 C1 D1
2   A2 B2 C2 D2
3   ...

Sheet 2:

     A                                     B
  1  can contain Sheet1.A1-Sheet1.AXX      Sheet1.D1-Sheet1.DXX
  2  ...

My question now is how can I have column B contain corresponding entry to the choice in column A. So when I choose Sheet1.A20 in Sheet2.A1 for example, then Sheet2.B1 should contain Sheet1.D20.

Cheers!

Solution

  • Does Sheet2.A1 contain the literal text 'Sheet1.A20' or a reference to Sheet1.A20?

    In the first case: Use text functions to examine the text in Sheet2.A1 and extract the reference, then refer to the cell you want using INDIRECT

    In the second case: Use VLOOKUP to find the value of Sheet2.A1 in Sheet1's A column and get the corresponding B column value.