 • 460K Members
• 9,719 Online
• 558K Conversations

# Help with formula

Hello,

I'm looking for help in rewriting this formula so that it increases by 10 rows at a time.  I haven't been able to figure out the correct sequence using OFFSET, but any solution would be much appreciated.

=IFERROR(LOOKUP(2,1/(ISNUMBER('PATIENT TRACKING'!R4:R13)),'PATIENT TRACKING'!R4:R13),"NO SCORE")

When I copy down I would like the second cell to reference R14:23 then the third to reference R24:33 and so on.

Thank you

4 Replies

# Re: Help with formula

Let say your first formula is in row #3. When

``OFFSET(\$R\$4,(ROW()-ROW(\$A\$3))*10,0,10)``

returns R4:R13 range. If drag it down on one cell, next returned range will be R14:R23.

# Re: Help with formula

@Sergei Baklan thank you for the response.

How would I correctly incorporate this into the formula I posted?

Thanks,

Daniel

# Re: Help with formula

Daniel, that could be like

``=IFERROR(LOOKUP(2,1/(ISNUMBER(OFFSET('PATIENT TRACKING'!\$R\$4,(ROW()-ROW(\$A\$3))*10,0,10))),OFFSET('PATIENT TRACKING'!\$R\$4,(ROW()-ROW(\$A\$3))*10,0,10)),"NO SCORE")``

if you first formula is within row 3 (e.g in B3).

# Re: Help with formula

Instead of the volatile OFFSET, a non-volatile alternative using INDEX would be:

=IFERROR(LOOKUP(2,1/(ISNUMBER(INDEX('PATIENT TRACKING'!R:R,4+(ROWS(\$1:1)-1)*10):INDEX('PATIENT TRACKING'!R:R,13+(ROWS(\$1:1)-1)*10))),

INDEX('PATIENT TRACKING'!R:R,4+(ROWS(\$1:1)-1)*10):INDEX('PATIENT TRACKING'!R:R,13+(ROWS(\$1:1)-1)*10)),

"NO SCORE")

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies