Forum Discussion

Copper Contributor

Jan 24, 2024

Formula to Skip Cells

I need help creating a formula to return values from a single cell to multiple cells (repeated) and to return values to nonconsecutive cells. So far I have created three which do one or the other but...

excel

Formulas and Functions

OliverScheurich

Gold Contributor

Jan 25, 2024

rgall0052420

=IF(COUNT(SEARCH(LEFT($A2,3),$A$2:$A2))=1,INDEX(H$2:H$8,MATCH(LEFT($A2,3),LEFT($G$2:$G$8,3),0)),"")

This formula returns the intended result if i correctly understand what you want to do. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is in cell B2 and filled across range B2:C29.

For illustration i've made an example where all the data is in one worksheet.

rgall0052420

Copper Contributor

Jan 26, 2024

OliverScheurich Thank you so much. That looks perfect. What would I change to make it duplicate/repeat the values every four cells instead of returning blanks for 3?

OliverScheurich
Gold Contributor
Jan 26, 2024
rgall0052420
=INDEX(H$2:H$8,MATCH(LEFT($A2,3),LEFT($G$2:$G$8,3),0))

You are welcome. If you want to return the result 4 times you can apply this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.