How to - Use Choose and Offset Functions?

How to Use Choose and Offset Functions?

In the following blog, keySkillset will take a look at two of the most used formulas in Excel. When you have a conditional action to do, they are the right ones to apply. We use Choose and Offset functions to operate select different outcomes depending on different conditions. Let’s take a closer look to Choose and Offset function separately.

‍

How to Use Choose Function?

Choose is the formula that will return a value from a list of cells based on a given condition. Choose has two different variables, which we will use for that formula. The first one is the index number. We will define the condition of which value that we are looking for in the cell list. The second one is the values. We will select all the values that we have in the cell range one by one and separate them with a comma. After that, we input our values into Choose function. Therefore,  it will look at the index number and gather the value from the list based on the index number.

If we look at the example below, we can see that the index number is selected as cell C2. We selected three different values, which are D4 for value1, D5 for value2 and D6 for value3. Since we have the value 1 inside the cell C2, the formula will give us the value1 as the output. However, we are changing the number in cell C2 and the outcome also changes. For example, if we change the value in C2 to 2, we will get the second value in the range, which is cell D5.

‍

‍

How Offset Function Works?

Offset is a formula with a reference cell. The goal is to find another cell depending on how many rows and columns is that cell away from the original one. For example, if you want to find the cell that is 2 rows below and 1 column right from cell C2, you will write Offset(C2, 2, 1). As you can understand from this formula, we used three different variables for the formula.

The first one is the reference cell where we will start moving from to reach our final cell. The second and the third ones show how many rows we want to move down. Additionally, it highlights how many columns we want to move right. Therefore, if we read the formula written above, we can start from cell C2 and we are moving 2 cells below and 1 cell right and return the value inside of that cell.

In the pictures below, we have an example of how Offset function works. We selected D2 as our reference cell, so this is where we will start from. First, we select C2 as our number of rows that we will move below. As a result, we put 0 in the column part and will only move in the same column. No columns will be changed. Since we have 1 in cell C2, we can read the formula we typed in D2 as start from cell D2.

The next step is to move only 1 row down and get the value inside that cell. Therefore, the Offset(D2, C2, 0) formula gave us the value in the cell D3. Similar as we have seen in Choose formula example, if we change the value in cell C2, we will get another cell. This is because we changed the number of rows we will move from the referenced cell.

‍