• 414K Members
• 8,754 Online
• 478K Conversations
SOLVED

New Contributor

# Excel Formula to skip rows for a specific text

How do get values from Section column to Result column for all the "Yes" under Response column and for any "No", the excel should skip that Section No and provide the number for next "Yes"?

 Section Response Result 1 Yes 1 2 Yes 2 3 Yes 3 4 No 5 5 Yes 6 6 Yes
4 Replies
Solution

# Re: Excel Formula to skip rows for a specific text

@subhashgc , actually you'd like to return first column filtered by condition on second one. That's like

`=FILTER(B3:B8,C3:C8="Yes")`

but since above function available only for Office Insiders we may imitate it by

`=IFERROR(INDEX(\$B\$3:\$B\$8,AGGREGATE(15,6,1/(\$C\$3:\$C\$8="Yes")*(ROW(\$C\$3:\$C\$8)-ROW(\$C\$2)),(ROW()-ROW(\$E\$2)))),"")`

in E3 above and drag it down.

Here AGGREGATE returns rows for which we meet the condition, we take next smallest and by this position INDEX returns the value from first column.

# Re: Excel Formula to skip rows for a specific text

This helps! Thanks a ton....

# Re: Excel Formula to skip rows for a specific text

@Sergei BaklanThanks a ton!

# Re: Excel Formula to skip rows for a specific text

@subhashgc , you are welcome

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies