Forum Discussion

LuukElsten's avatar
LuukElsten
Copper Contributor
Oct 18, 2023
Solved

Skipping cells in formula

Hi, I have made an excel file where I have data in 'blocks', not a list. I want to make a summary tab, but I can't find a way to make the formula in a way where I can put in 2 items and then 'pull' ...
Formulas and Functions
  • LeonPavesic's avatar
    LeonPavesic
    Oct 18, 2023

    Hi LuukElsten,

    you can use the following formula:=INDEX(L:L, (ROW(A2) - 2) * 3 + 17) 

    This formula will return the value from column L, starting at row 17 and skipping every other row. So, in cell A2, the formula will return the value from cell L17, in cell A3, it will skip a row and return the value
    from cell L41, and so on.

    This formula is equivalent to the OFFSET formula that I provided in my previous response. However, the INDEX function is generally considered to be more efficient than the OFFSET function, especially when working with large datasets.

    To use the INDEX function, simply copy and paste it into cell A2 and then copy it down to cell A500 (or however many cells you need). Excel will automatically fill in the correct values for each row.

    Here is an example of how to use the formula:

    Cell | Formula | Result
------- | -------- | --------
A2 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L17
A3 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L41
A4 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L65
A5 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L89
A6 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L11


    Please click Mark as Best Response & Like if my post helped you to solve your issue.

    This will help others to find the correct solution easily. It also closes the item.

    If the post was useful in other ways, please consider giving it Like.

    Kindest regards,

    Leon Pavesic
    (LinkedIn)

     

LeonPavesic's avatar
LeonPavesic
Silver Contributor
Oct 18, 2023

Hi LuukElsten,

you can use the following formula:=INDEX(L:L, (ROW(A2) - 2) * 3 + 17) 

This formula will return the value from column L, starting at row 17 and skipping every other row. So, in cell A2, the formula will return the value from cell L17, in cell A3, it will skip a row and return the value
from cell L41, and so on.

This formula is equivalent to the OFFSET formula that I provided in my previous response. However, the INDEX function is generally considered to be more efficient than the OFFSET function, especially when working with large datasets.

To use the INDEX function, simply copy and paste it into cell A2 and then copy it down to cell A500 (or however many cells you need). Excel will automatically fill in the correct values for each row.

Here is an example of how to use the formula:

Cell | Formula | Result
------- | -------- | --------
A2 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L17
A3 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L41
A4 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L65
A5 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L89
A6 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L11


Please click Mark as Best Response & Like if my post helped you to solve your issue.

This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

 

Resources