SOLVED
Home

Offset with multiple range width

%3CLINGO-SUB%20id%3D%22lingo-sub-354057%22%20slang%3D%22en-US%22%3EOffset%20with%20multiple%20range%20width%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354057%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20was%20looking%20for%20help%20with%20the%20following%20offset%20question%2C%20thanks%20for%20any%20help%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20the%20current%20offset%20formulas%3A%3C%2FP%3E%3CP%3Ecell%20reference%26nbsp%3B%20A20%20%3D%201%2C%20B20%3D%203%2C%20C20%3D%207%2C%20D20%3D%209%2C%20E20%3D14%2C%20F20%3D%2015%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOFFSET(A20%2C0%2C4%2C1%2C1)%26nbsp%3B%20Result%20-%2014%3C%2FP%3E%3CP%3ESUM(OFFSET(A20%2C0%2C4%2C1%2C2))%20Result%20-%2029%3C%2FP%3E%3CP%3EQuestion%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Eis%20there%20away%20to%20have%20the%20width%20give%20the%20value%20instead%20of%20summing%2C%20(%20The%20value%20returned%20would%20be%20E20%20%26amp%3B%20F20)%20Result%2014%2015%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-354057%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354661%22%20slang%3D%22en-US%22%3ERe%3A%20Offset%20with%20multiple%20range%20width%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354661%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Haytham%20works%20like%20a%20charm!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354659%22%20slang%3D%22en-US%22%3ERe%3A%20Offset%20with%20multiple%20range%20width%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354659%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Haytham%20-%20works%20like%20a%20charm!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354376%22%20slang%3D%22en-US%22%3ERe%3A%20Offset%20with%20multiple%20range%20width%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354376%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20an%20easy%20solution%20using%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FTEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ETEXTJOIN%20function%3C%2FA%3E%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2COFFSET(A20%2C0%2C4%2C1%2C2))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20791px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F83819i5EF88BBB517C917B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22TEXTJOIN%20%26amp%3B%20OFFSET.png%22%20title%3D%22TEXTJOIN%20%26amp%3B%20OFFSET.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20please%20note%20that%20the%20TEXTJOIN%20is%20only%20available%20in%20Excel%202019%20or%20Office%20365%3C%2FP%3E%3CP%3EIf%20you%20have%20an%20earlier%20version%20of%20Excel%2C%20you%20will%20not%20see%20this%20function!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20earlier%26nbsp%3Bversions%20of%20Excel%2C%20I%20would%20suggest%20this%20workaround%20using%20INDEX%20and%20concatenation%20operator%3A%3C%2FP%3E%3CPRE%3E%3DINDEX(OFFSET(A20%2C0%2C4%2C1%2C2)%2C1)%26amp%3B%22%2C%20%22%26amp%3BINDEX(OFFSET(A20%2C0%2C4%2C1%2C2)%2C2)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F83821iAE0C706A85396061%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22TEXTJOIN%2C%20OFFSET%20%26amp%3B%20INDEX.png%22%20title%3D%22TEXTJOIN%2C%20OFFSET%20%26amp%3B%20INDEX.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354314%22%20slang%3D%22en-US%22%3ERe%3A%20Offset%20with%20multiple%20range%20width%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354314%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20idea%20if%20this%20can%20be%20done%2C%20or%20is%20the%20question%20not%20clear%3F%26nbsp%3B%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
ram12020
New Contributor

Hi,

I was looking for help with the following offset question, thanks for any help:

 

Using the current offset formulas:

cell reference  A20 = 1, B20= 3, C20= 7, D20= 9, E20=14, F20= 15 

 

OFFSET(A20,0,4,1,1)  Result - 14

SUM(OFFSET(A20,0,4,1,2)) Result - 29

Question

is there away to have the width give the value instead of summing, ( The value returned would be E20 & F20) Result 14 15

 

4 Replies

Any idea if this can be done, or is the question not clear?  thanks

Solution

Hi,

 

There is an easy solution using TEXTJOIN function as follows:

=TEXTJOIN(", ",TRUE,OFFSET(A20,0,4,1,2))

TEXTJOIN & OFFSET.png

 

But, please note that the TEXTJOIN is only available in Excel 2019 or Office 365

If you have an earlier version of Excel, you will not see this function!

 

For earlier versions of Excel, I would suggest this workaround using INDEX and concatenation operator:

=INDEX(OFFSET(A20,0,4,1,2),1)&", "&INDEX(OFFSET(A20,0,4,1,2),2)

TEXTJOIN, OFFSET & INDEX.png

 

 

Hope that helps

Thanks Haytham - works like a charm!

Thanks Haytham works like a charm!!

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