Excel Formula to Move Cursor and Copy a Cell

The formula to move cursor until it finds something, then copies a cell in Excel is based on the following logic:

• If the value in column A is 3 digits long, then copy the value in column B from the same row.
• If the value in column A is not 3 digits long, then go up until you find the first value with 3 digits, and then copy the value in column B from that row.

This formula can be useful when you have data that is grouped into sets, and you want to assign a common value to each set based on the first value with 3 digits in column A.

Procedures

To create the formula, you need to use the IF function, the AND function, the LEN function, and a relative reference. The IF function tests a condition and returns one value if true, and another value if false. The AND function returns TRUE if all the arguments are TRUE, and FALSE otherwise. The LEN function returns the number of characters in a text string. A relative reference is a cell address that changes when the formula is copied to another cell.

The formula has the following syntax:

=IF(AND(A4>=100,A4<1000),B4,C3)

This formula means:

• Check if the value in A4 is between 100 and 999, which means it has 3 digits.
• If yes, then return the value in B4, which is the same row.
• If no, then return the value in C3, which is the row above.

To apply the formula to the entire column C, you need to copy the formula from C4 and paste it to the rest of the cells. The formula will adjust the cell references automatically based on the relative position.

Explanation

To understand how the formula works, let’s look at an example with some sample data. Suppose you have the following data in columns A and B:

You want to fill column C with the values from column B, but only for the rows that have 3 digits in column A. For the rows that have 4 digits in column A, you want to copy the value from the previous row that has 3 digits. The expected result is:

To achieve this result, you can use the formula =IF(AND(A4>=100,A4<1000),B4,C3) in cell C4, and then copy it down to the rest of the cells in column C. Here is how the formula works for each cell:

• In cell C4, the formula evaluates to =IF(AND(123>=100,123<1000),B4,C3). The condition inside the AND function is TRUE, because 123 is between 100 and 999. Therefore, the IF function returns the value in B4, which is ABC.
• In cell C5, the formula evaluates to =IF(AND(456>=100,456<1000),B5,C4). The condition inside the AND function is TRUE, because 456 is between 100 and 999. Therefore, the IF function returns the value in B5, which is DEF.
• In cell C6, the formula evaluates to =IF(AND(789>=100,789<1000),B6,C5). The condition inside the AND function is TRUE, because 789 is between 100 and 999. Therefore, the IF function returns the value in B6, which is GHI.
• In cell C7, the formula evaluates to =IF(AND(1000>=100,1000<1000),B7,C6). The condition inside the AND function is FALSE, because 1000 is not less than 1000. Therefore, the IF function returns the value in C6, which is GHI.
• In cell C8, the formula evaluates to =IF(AND(1001>=100,1001<1000),B8,C7). The condition inside the AND function is FALSE, because 1001 is not less than 1000. Therefore, the IF function returns the value in C7, which is GHI.
• In cell C9, the formula evaluates to =IF(AND(1002>=100,1002<1000),B9,C8). The condition inside the AND function is FALSE, because 1002 is not less than 1000. Therefore, the IF function returns the value in C8, which is GHI.
• In cell C10, the formula evaluates to =IF(AND(234>=100,234<1000),B10,C9). The condition inside the AND function is TRUE, because 234 is between 100 and 999. Therefore, the IF function returns the value in B10, which is STU.
• In cell C11, the formula evaluates to =IF(AND(567>=100,567<1000),B11,C10). The condition inside the AND function is TRUE, because 567 is between 100 and 999. Therefore, the IF function returns the value in B11, which is VWX.
• In cell C12, the formula evaluates to =IF(AND(890>=100,890<1000),B12,C11). The condition inside the AND function is TRUE, because 890 is between 100 and 999. Therefore, the IF function returns the value in B12, which is YZ.

Scenario

To illustrate the formula with a real-world scenario, let’s assume that you have a list of products and their prices in columns A and B. Each product belongs to a category, which is indicated by a 3-digit code in column A. The products within each category have a 4-digit code in column A. You want to fill column C with the category names, which are stored in column B for the rows that have 3 digits in column A. For the rows that have 4 digits in column A, you want to copy the category name from the previous row that has 3 digits. The sample data and the expected result are shown below:

To achieve this result, you can use the same formula as before: =IF(AND(A4>=100,A4<1000),B4,C3) in cell C4, and then copy it down to the rest of the cells in column C. The formula will work the same way as explained in the previous section, but with different values.

Calculation and result

The calculation of the formula is based on the logic and the procedures described above. The result of the formula is the value that is returned in column C for each row, based on the value in column A and column B. The result of the formula for the sample data is shown in the table below:

Other approaches

There are other possible ways to achieve the same result as the formula. Here are some alternative approaches:

• Using the VLOOKUP function: You can use the VLOOKUP function to look up the category name based on the first 3 digits of the product code. The formula in cell C4 would be =VLOOKUP(LEFT(A4,3),A:B,2,FALSE), and then copied down to the rest of the cells in column C. The LEFT function extracts the first 3 characters of the product code, and the VLOOKUP function searches for that value in the first column of the range A:B, and returns the corresponding value in the second column. The FALSE argument indicates that the lookup value must match exactly.
• Using the INDEX and MATCH functions: You can use the INDEX and MATCH functions to return the category name based on the first 3 digits of the product code. The formula in cell C4 would be =INDEX(B:B,MATCH(LEFT(A4,3),A:A,0)), and then copied down to the rest of the cells in column C. The INDEX function returns the value in a given position in a range, and the MATCH function returns the position of a given value in a range. The LEFT function extracts the first 3 characters of the product code, and the MATCH function searches for that value in the range A:A, and returns the row number. The INDEX function then returns the value in that row number in the range B:B. The 0 argument indicates that the lookup value must match exactly.
• Using a helper column: You can use a helper column to store the category name for each row, and then use a simple reference to copy it to column C. The formula in cell D4 would be =IF(LEN(A4)=3,B4,D3), and then copied down to the rest of the cells in column D. The LEN function returns the number of characters in a text string, and the IF function tests if the value in column A has 3 characters. If yes, then the formula returns the value in column B, which is the category name. If no, then the formula returns the value in the previous row in column D, which is the previous category name. The formula in cell C4 would be =D4, and then copied down to the rest of the cells in column C. This formula simply copies the value from column D to column C.