+ Reply to Thread
Results 1 to 45 of 45

Index Match with Indirect and Named Range

  1. #1
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Index Match with Indirect and Named Range

    Hi,

    I'm trying to build a somewhat complex formula and am stuck on one portion (INDEX MATCH). I stripped that section out just to test it but it doesn't seem to work the way I expect it to.

    Details:

    I have a named range that contains the names of various worksheets (Worksheet1, Worksheet2, Worksheet3...). I am trying to reference this named range as an array in my INDEX MATCH - therefore I used INDIRECT. However, when I use the INDIRECT within the INDEX/MATCH, it does not return the array when I go evaluate the formula; it returns "Worksheet1".

    Please Login or Register  to view this content.
    The goal is to see this when I evaluate the INDIRECTs: "Worksheet1!C1:C10";"Worksheet2!C1:C10";"Worksheet3!C1:C10"... and so on. I've tried using INDIRECT with this exact same syntax through SUMIF
    Please Login or Register  to view this content.
    and it produces the result I'm looking for. Anyone have insight on why it doesn't work with INDEX MATCH? Thanks!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,165

    Re: Index Match with Indirect and Named Range

    Hi and welcome to the forum.

    Seems to work for me.
    NamedRange is presumably a sheet tab name.

    If so it's probably because you have a space in the tab name.

    Try instead

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    note the all important ' characters which are vital where the tab name characters are not contiguous and contain one or more spaces.

    If this is not the case then please upload the workbook
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Re: Index Match with Indirect and Named Range

    Hi Richard,

    Thanks for your welcome and response!

    The NamedRange represents various worksheet names. None of the worksheet names have spaces in them. I created an example workbook to show the formula and I am using. It will not return anything in its current form, but I would just like to see the results in the Evaluate Formula option to show as "Worksheet1!C1:C10";"Worksheet2!C1:C10";"Worksheet3!C1:C10 and so on.
    Attached Files Attached Files
    Last edited by hikari8; 01-27-2020 at 03:09 PM.

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    hikari8, What are you going to get?

  5. #5
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Re: Index Match with Indirect and Named Range

    Hi BMV, sorry, I'm not sure what you're asking

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,165

    Re: Index Match with Indirect and Named Range

    I'm still not clear what results you expect and why your MATCH() is liiking to match A1 on Worksheet1 (which is blank) with B1:B10 on Sheet1.

    Would you manually add the results you expect to see and indicate the which are the result cells. It alwasy he;ps of we have real data to work with and know what the actual result required is.

  7. #7
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Re: Index Match with Indirect and Named Range

    The workbook is just a stripped down version with the necessary components for the formula. I can put together a workbook with actual numbers later on but the full formula has a SUMPRODUCT to combine the results. I wanted to isolate the INDEX/MATCH piece because that is what's giving me an issue right now. Additionally, the C1:C10 and B1:B10 ranges in the formula do not refer to Sheet1 specifically. The purpose of my INDIRECT formula is to combine the values in my named range (WSNames) with the C1:C10 and B1:B10 ranges. When I go to Formulas -> Evaluate Formula and click through step by step, I want to see the
    Please Login or Register  to view this content.
    piece to be showing up as {"Worksheet1";"Worksheet2";"Worksheet3";"Worksheet4";"Worksheet5"}, because that is what is in the named range WSNames. The next step should then combine each value with the C1:C10 or B1:B10 to get {"Worksheet1!C1:C10";"Worksheet2!C1:C10";"Worksheet3!C1:C10";"Worksheet4!C1:C10";"Worksheet5!C1:C10"}.

    If you put the below formula in any cell and click through the Evaluate Formula, you'll see what I mean (sorry I forgot to include it in the previous workbook).
    Please Login or Register  to view this content.
    Last edited by AliGW; 01-28-2020 at 04:13 AM. Reason: Please don't quote unnecessarily!

  8. #8
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    hikari8 Actualy my language skills more less then Excel skills, sorry. But I'm still confused. Could you prepare example with some manually calculated results.

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,165

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by BMV View Post
    hikari8 Actualy my language skills more less then Excel skills, sorry. But I'm still confused. Could you prepare example with some manually calculated results.
    @BMV

    This is not meant as any crticism, merely a helpful comment for your further benefit in the English language.
    We'd usually say: "Actually my (English) language skills are fewer than my Excel skills.

    We use 'fewer' for countable things and 'less' for stuff that isn't countable, like say water in a jar.

  10. #10
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    Off
    Richard Buttrey, Thank you, and I would be glad if somebody correct my messages (typos, gramma e.t.c). Practice is one of the reason to be here and I hope all of native English speakers forgive me.

  11. #11
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Index Match with Indirect and Named Range

    You can't do a 3d MATCH, if that's what you're asking... so, to BMVs posts, I think you need to outline your desired output relative to your conditional tests.

    I suspect you may be looking to return the first match as opposed to the aggregate of all matches... ?

    Conditional 3d calculations are generally inefficient, and Volatile, and UDFs are often preferable in that respect.

  12. #12
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by XLent View Post
    You can't do a 3d MATCH, if that's what you're asking... so, to BMVs posts, I think you need to outline your desired output relative to your conditional tests.

    I suspect you may be looking to return the first match as opposed to the aggregate of all matches... ?

    Conditional 3d calculations are generally inefficient, and Volatile, and UDFs are often preferable in that respect.
    Hi XLent, thanks for your input. I think you might be right that I am trying to achieve a 3D calculation. I'm not really familiar with these kinds of formulas so I don't really know what alternatives (UDF) are available. Please see attached workbook where I have the full formula in Worksheet1 cell D2. The result I want is the sum of AD across row 5 and sum of 12345-1234 down column B, for all the worksheets in the named range WSNames.
    Attached Files Attached Files
    Last edited by hikari8; 01-28-2020 at 04:16 PM.

  13. #13
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    I'm not sure it's good idea to use this formula but
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by BMV; 01-29-2020 at 06:04 AM.

  14. #14
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hikari8 View Post
    . . . =SUMPRODUCT(SUMIF(INDIRECT(WSNames&"!C1:C10"),A1,INDIRECT(WSNames&"!B1:B10")))
    Conditional summing across worksheets. If WSNames contains worksheet names without delimiting single quotes, safer to add them regardless.

    =SUMPRODUCT(SUMIF(INDIRECT("'"&WSNames&"'!C1:C10"),A1,INDIRECT("'"&WSNames&"'!B1:B10")))

    That should work.

    However, if you need many formulas like this, INDIRECT calls can really slow down recalculation. If so, you'd be far better off using your list of worksheet names in Sheet1. In Sheet1!B2, =INDEX(Worksheet1!$B:$B,COLUMNS($B2:B2)), then fill B2 right into C2:K2. In Sheet1L2, =INDEX(Worksheet1!$C:$C,COLUMNS($L2:L2)), then fill L2 right into M2:U2. Do the same in the subsequent rows of Sheet1, replacing Worksheet1 with Worksheet2 in row 3, etc.

    If you really want to make this robust, use formulas in col A for worksheet names, e.g., in Sheet1!A2,

    =REPLACE(CELL("Filename",Worksheet1!$A$1),1,FIND("]",CELL("Filename",Worksheet1!$A$1)),"")

    Those will recalc to new worksheet names if you rename worksheets, but they'll return #REF! errors if you delete row 1 or col A or CUT another cell and paste into cell A1 in such worksheets. Then in Sheet1!B2,

    =INDEX(CHOOSE(ROWS(B$2:B2),Worksheet1!$B:$C,Worksheet2!$B:$C,Worksheet3!$B:$C,Worksheet4!$B:$C,Worksheet5!$B:$C),COLUMNS($B2:B2),1)

    Copy B2 and paste into B2:K6. In Sheet1!L2,

    =INDEX(CHOOSE(ROWS(L$2:L2),Worksheet1!$B:$C,Worksheet2!$B:$C,Worksheet3!$B:$C,Worksheet4!$B:$C,Worksheet5!$B:$C),COLUMNS($L2:L2),2)

    Copy L2 and paste into L2:U6.

    Then you could use formulas like

    =SUMIF(Sheet1!$L$2:$U$6,A1,Sheet1!$B$2:$K$6)

    I've worked with workbooks with thousands of formulas calling INDIRECT. It ain't pleasant. If you're building a wokbook other people would use, you WON'T enhance your reputation if you use a lot of formulas with INDIRECT calls.

  15. #15
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    hrlngrv. the main problem is not indirect but 3d calculation. And Indirect, Offset, CELL… are volatile functions and are not slow but calculated every calculation and because of this workbook looks like turtle in the case of many volatile on the sheets.

  16. #16
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hikari8
    ...I'm not really familiar with these kinds of formulas so I don't really know what alternatives (UDF) are available
    so, the Function written below is very much tailored to your ask, and brute force, but should give you an idea?

    the code should be stored in a standard module, and could then be called from a cell per below:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    - first parameter is sheet reference (named range in the above)
    - second parameter is the range of interest (on each tab)
    - third parameter is the Vertical criteria -- if blank it will be ignored
    - fourth parameter is the Horizontal criteria -- if blank it will be ignored

    Please Login or Register  to view this content.

  17. #17
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    XLent, Application .volatile is forgotten. If not then no calculation will be done after changing any values on the others sheets.

  18. #18
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Index Match with Indirect and Named Range

    @BMV, have you actually tested that assertion...

  19. #19
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    XLent , No, but I can read code . Now I did. Use UDF and change Worksheet2!H9 to any. Not Worksheet2 and not work sheets list.
    also for UDF you can check are sheets present or not, now it is error only.

    Please Login or Register  to view this content.
    Last edited by BMV; 01-29-2020 at 06:51 AM.

  20. #20
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Index Match with Indirect and Named Range

    -- re: volatile - we'll agree to disagree

    -- the addition of an ISREF test is of (very) debatable merit as it masks invalid references

    -- the removal of the IsArray prevents a single worksheet being passed explicitly, e.g. "Worksheet1"
    (whilst I would not argue that the above is a reasonable use-case it is why the IsArray test was in place in the first instance)

    sometimes it is better not to change things for the sake of changing things - but I certainly appreciate the enthusiasm...

  21. #21
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    - the addition of an ISREF test is of (very) debatable merit as it masks invalid references
    ok it could be replace to error handling

    Please Login or Register  to view this content.
    Good point. Actually I tested with reference to one cell but not single constant. Thanks.

    Please Login or Register  to view this content.
    Last edited by BMV; 01-29-2020 at 08:50 AM.

  22. #22
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by XLent View Post
    . . . E2: =SUM3D(WSNames,$B$5:$K$10,$B$3,$B$2) . . .
    Your UDF doesn't include Application.Volatile, so it may not recalculate when you believe it would.

    I'm annotating this because XLent doesn't want to report back what happens at each of these steps. I'm running Office 365 MSO (16.0.12508.20000) 32-bit under Windows 10 Insider build 19551.

    Missed a step: enter XLent's SUM3D UDF code in a general module of the new workbook first. Sorry for that oversight.

    Make a workbook with worksheets named results, A, B and C. First, put the formula in the results worksheet as

    D1: =SUM3D({"A";"B";"C"},A1:C3,X99,X99)

    where results!X99 is blank, so criteria always true. At this point, D1 returns 0, as it should.

    In worksheet A,

    B2: =results!A1
    B3: 1002001
    C2: 1001002
    C3: 1002002

    In worksheet B,

    B2: =results!B1
    B3: 20020010
    C2: 20010020
    C3: 20020020

    B2: =results!C1
    B3: 300200100
    C2: 300100200
    C3: 300200200

    Return to the results worksheet. What does its cell D1 show? Since there should be nothing entered in results!A1:C1, shouldn't it return 963555555? Does it? D1 still returns 0. Press [F9] a few times. What does D1 show then? D1 still returns 0.

    Now enter values in results!A1:C1. Does the value shown in results!A1 change? I enter 1 in A1, 2 in B1 and 3 in C1, and D1 returns 963555561, the same result as =SUM('A:C'!B2:C3). It should because values in its A1:C3 argument changed.

    The point here is that Excel sees arguments A1:C3 and X99 twice, all of which appear without worksheet qualifiers, so Excel believes that those refer to the worksheet which contains the cell with the formula calling SUM3D. OTOH, Excel has no idea whatsoever that it should really depend on the A1:C3 range in worksheets A, B and C.

    Now change the B2 formulas in A, B and C to refer to row 5. Return to the results worksheet. What does cell D1 show? D1 still returns 963555561, which is now incorrect. Now change values in A5:C5. The values in A:C!B2 should change, but does the value in results!D1 change? I enter -1 in A5, -2 in B5 and -3 in C5, and D1 still returns 963555561.

    There's a trick to add ad hoc volatility to UDFs in cases like this. Add an optional 5th Variant argument (I prefer to name it kludge). Add nothing else to your code, so that kludge isn't used in it. Then use a formula like

    results!D1: =SUM3D({"A";"B";"C"},A1:C3,X99,X99,COUNTA(A:C!A1:C3))

    Now anytime anything in A:C!A1:C3 changes, the value of results!D1 changes. Why? Because the otherwise useless COUNTA call in the 5th argument makes the formula depend on the cells in the relevant 3D block.

    FTHOI, I saved the workbook I was using and opened it in Excel 2010 under Windows 7, and it has the same cell dependency problems. Maybe this works differently on XLent's machine, but I very much doubt it. Thus the need for thorough testing.
    Last edited by hrlngrv; 01-30-2020 at 06:39 PM.

  23. #23
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by BMV View Post
    . . . main problem is not indirect but 3d calculation.
    Understood.

    And Indirect, Offset, CELL… are volatile functions and are not slow but calculated every calculation . . .
    Also understood. My warning still holds. If there would be A LOT of formulas calling volatile functions, especially if those volatile functions returned references to large ranges which needed lots of subsequent processing, matching, lookups, LARGE, SMALL, etc., needing to recalc them every time ANYTHING triggers recalc can make Excel very sluggish.

    To be clear: it's not the volatile function calls themselves which are slow, it's the number of formulas calling them AND what it needs to do with the results of calling them. FWIW, CELL, NOW, and TODAY are NBD even if used in thousands of formulas. RAND and RANDBETWEEN themselves aren't necessarily a problem as long as not all that many other cell formulas depend on the ranges with formulas calling them. OTOH, INDIRECT and OFFSET used in even hundreds of formulas become problematic.

  24. #24
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by XLent View Post
    . . . have you actually tested that assertion...
    For some people and some things testing isn't needed. Some errors are easy for some of us to spot.

  25. #25
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Re: Index Match with Indirect and Named Range

    Thank you everyone for all your time and inputs!

    -XLent, thanks for writing out a tailored UDF for my specific situation. I tried using it in the example workbook and worked very well. However, I tried copying the same UDF into my larger spreadsheet and it doesn't seem to work well. Due to the size of my spreadsheet, the formulas don't load properly and it takes forever to calculate/save. For more background, I use the sum3d formula across many columns and several worksheets (one row per worksheet, across ~200 columns). Each column is referencing a different horizontal criteria (AA, AB, AC, AD) while each worksheet references a different vertical criteria (12345-1234, 11111-1111, etc). Just speculating, but does the UDF not work as well in a large spreadsheet? Thanks!

    -hrlngrv, would you be able to comment on the above? I do believe a UDF would be the most reasonable solution. I currently have a workaround but it utilizes a lot of INDIRECTs so it is also slow.
    Last edited by hikari8; 01-29-2020 at 06:14 PM.

  26. #26
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hikari8 View Post
    . . . hrlngrv, would you be able to comment on the above? . . . I currently have a workaround but it utilizes a lot of INDIRECTs so it is also slow.
    Try BMV's latest UDF. IMO, the UDF approach needs Application.Volatile OR an additional optional argument which could force needed dependent ranges.

    If the data in these worksheets were changing frequently, you may have to learn to tolerate slow recalc. The most reliable way to speed things up in that situation is to consolidate all data into one worksheet, then use nonvolatile functions to process that consolidated single-worksheet data. Bluntly, the LATEST version of Excel is still a DECADE behind Lotus 1-2-3 from the 1990s in terms of 3D functionality. Always best to avoid anything beyond the subfunctions the AGGREGATE function provides for 3D references. That DOESN'T include conditional summing.

    Actually, one other approach. If all the worksheets you'd be processing were sequential, i.e., no other worksheets between them you wouldn't want to process, BETTER BY FAR to put 2D functions in each of these worksheets, say beginning in col AAA, so AAA1: =SUMPRODUCT((B1:HZ1=col_crit)*(A2:A500000=row_crit),B2:HZ500000) then insert a new worksheet just before the first of these worksheets and name that new worksheet alpha, insert a new worksheet just after the last of these worksheets and name that new worksheet omega, then calculate the conditional sum across these worksheets as

    =SUM(alpha:omega!$AAA$1)

    To make that even easier, select the first of these worksheets, then scroll the worksheet tabs to the last of these worksheets, hold down [Shift] and click on the last worksheet's tab. That should group all your worksheets, then enter the cell AAA1 formula above in all of them at once. I partially retract my 1-2-3 comment above: Excel's worksheet grouping is very handy, Sorry I didn't think of it before. Anyway, use 2D approaches WITHOUT volatile function calls where they make sense, then use simple 3D-capable nonvolatile functions to aggregate all the worksheets' results. That's the best way to do this sort of thing in Excel.

    IOW, 3D conditional summing using either INDIRECT or UDFs may look cool, but they suck for recalc. Better recalc performance requires doing things piece by piece, using the best approach for each separate piece. This approach will cause far fewer headaches in the long run.
    Last edited by hrlngrv; 01-29-2020 at 07:21 PM.

  27. #27
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Re: Index Match with Indirect and Named Range

    I'll give BMV's UDF a try tomorrow (thanks BMV!). I don't really understand the concept of volatility but I can kinda see where you guys are going with it.

    The data in my spreadsheet will change maybe a couple times a day, so if its a little slow I can tolerate it. However, the UDF I tried previously was taking 30 minutes to save (maybe my laptop is just slow).

    I did think about using a formula across several worksheets like you mentioned with alpha:omega! - but I thought I would have to use an array formula (which tends to slow down workbooks too right?) like the one I have in the example workbook (SUM(IF(Array1 = Condition1, IF(Array2, Condition2, Sum Range))). Not very good with SUMPRODUCT but I will give it a try and let you know.

    In terms of slow recalcs - it seems that INDIRECT places close to or at the front of the list. Do you know how array formulas, UDFs and SUMPRODUCT compare?

  28. #28
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hikari8 View Post
    . . . I don't really understand the concept of volatility but I can kinda see where you guys are going with it. . . .
    Simple example. In a new workbook, in its 1st worksheet, B2: =C2, and fill B2 down into B3:B9, B10: =SUM(B2:B9). Enter numbers in C2:C9, and they flow into corresponding cells in B2:B9, and the formula in B10 also reflects those changes. Changing cells in C2:C9 triggers recalc in B2:B10. Now enter numbers in A2:A9. No change in B2:B10 because nothing changed in cells to which the formulas in those cells refer. In that sense, the SUM function in B10 is nonvolatile. It recalcs only when cells to which it refers, namely, B2:B9, change. Now change B2's formula to =INDIRECT("C2"). Enter numbers in A2:A9, and while C2 is unchanged, Excel will recalc B2 and so also B10. INDIRECT doesn't know ahead of time the cells on which it depends, so it recalcs whenever ANYTHING triggers recalc.

    . . . I did think about using a formula across several worksheets like you mentioned with alpha:omega! - but I thought I would have to use an array formula
    Nope. =SUM(alpha:omega!AAA1) isn't an array formula. Simple 3D aggregations (COUNT, COUNTA, SUM, AVERAGE, MIN, MAX, etc) aren't array formulas.

    (which tends to slow down workbooks too right?)
    Depends on the array formulas. Some, e.g., =SUM(IF(one_condition,IF(another_condition,IF(yet_another_condition,values)))) can be faster than equivalents (in this case SUMIFS) because items in another_condition are only evaluated for corresponding TRUE items in one_condition, and likewise items in yet_another_condition are only evaluated for corresponding TRUE items in one_condition AND another_condition. OTOH, LARGE(...,array) and SMALL(...,array) can be problematic.

    As for recalc speed, same conditional sums in all worksheets of interest, then simple SUM of those cells across all worksheets is likely to recalc a lot faster than any formulas using INDIRECT or udfs using Application.Volatile. CLARIFICATION: if the affected cells in the worksheets of interest change, then recalc involving INDIRECT or nonvolatile approaches should take about the same time; however, changes in OTHER CELLS which don't affect the conditional sums of interest won't trigger recalc of SUMIFS on each worksheet of interest. That's where you save recalc time.
    Last edited by hrlngrv; 01-30-2020 at 12:44 AM.

  29. #29
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hrlngrv View Post
    Nope. =SUM(alpha:omega!AAA1) isn't an array formula. Simple 3D aggregations (COUNT, COUNTA, SUM, AVERAGE, MIN, MAX, etc) aren't array formulas.
    I was referring to the formula I posted in my example workbook - Worksheet1!E2. When I'm trying to do a SUMIF with multiple look ups horizontal and vertical, I thought it had to be an array formula to work haha.

    I'll try working with your other suggestions tomorrow and hopefully yield good results.

  30. #30
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hikari8 View Post
    . . . trying to do a SUMIF with multiple look ups horizontal and vertical, I thought it had to be an array formula to work . . .
    If you mean something like =SUMPRODUCT((B1:F1=x)*(A2:A100=y),B2:F100), yes, it involves arrays, and it involves a lot of multiplications by 1 or 0. The good news is that multiplications by 1 and 0 are as fast as multiplications get. Thing is, there's no way to make this particular thing much faster. You could try =SUM(IF(B1:F1=x,IF(A2:A100=y,B2:F100))) entered as an array formula. If there are lots of FALSE results in the 2 comparisons, this could be faster than the SUMPRODUCT equivalent.

  31. #31
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    hrlngrv, if you have possibility to insert simple formula for every sheet and consolidate result it's right way. I hope you have seen formula in #13. it's only example and Looks like crazy. UDF not always but better in this case but also must be volatile and in the case of huge data will be slow.

  32. #32
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hrlngrv View Post
    You could try =SUM(IF(B1:F1=x,IF(A2:A100=y,B2:F100))) entered as an array formula.
    This is exactly what I mean. I used this in my workbook to do a vertical and horizontal lookup while summing all the matches.

  33. #33
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by BMV View Post
    . . . UDF not always but better in this case but also must be volatile . . .
    No. There's an alternative.

    Please Login or Register  to view this content.
    Call this as =foo(SheetA!B3,SheetZ!X100) and it won't recalc when, say, SheetA!X100 or SheetZ!B3 change. OTOH, call it as =foo(SheetA!B3,SheetZ!X100,COUNTA(SheetA:SheetZ!B3:X100)) and it will recalc when any cell in SheetA:SheetZ!B3:X100 changes. Ad hoc way to give UDFs proper dependent cells.

  34. #34
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    hrlngrv . COUNTA(SheetA:SheetZ!B3:X100) - depends on Sheets arrangement. If SheetB is moved before SheetA function skip it from calculation. But this trick can be used.

  35. #35
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by BMV View Post
    . . . COUNTA(SheetA:SheetZ!B3:X100) - depends on Sheets arrangement. If SheetB is moved before SheetA . . .
    Indeed, but tul and blr arguments would also be affected in terms of any sensible way to iterate through worksheets between them. Just as =SUM(SheetA:SheetZ!B3:X100) would be affected by moving sheets before SheetA or after SheetZ. If someone were stupid enough to CUT/PASTE on top of cells and/or delete rows/columns, the COUNTA argument can be reduced to #REF!. Bad things can happen when people do stupid and/or careless things.

    If this is for workbooks others would use, there's always workbook protection to prevent changing the structure of the workbook.

    Finally there's BRUTE FORCE,

    COUNTA(SheetA!B3:X100,SheetB!B3:X100,SheetC!B3:X100,SheetD!B3:X100,SheetE!B3:X100,SheetF!B3:X100,SheetG!B3:X100,SheetH!B3:X100,
    SheetI!B3:X100,SheetJ!B3:X100,SheetK!B3:X100,SheetL!B3:X100,SheetM!B3:X100,SheetN!B3:X100,SheetO!B3:X100,SheetP!B3:X100,SheetQ!B3:X100,
    SheetR!B3:X100,SheetS!B3:X100,SheetT!B3:X100,SheetU!B3:X100,SheetV!B3:X100,SheetW!B3:X100,SheetX!B3:X100,SheetY!B3:X100,SheetZ!B3:X100)

    There are further tricks. Defining names like WSG1 referring to =CHOOSE(1,0,SheetA!$B$3:$X$100, . . .,SheetZ!$B$3:$X$100), WSG2 referring to =CHOOSE(1,0,SheetAA!$B$3:$X$100, . . .,SheetAZ!$B$3:$X$100), etc, then =COUNTA(WSG1,WSG2, . . ., WSG99). Heck, define the name WSGS referring to that COUNTA call, and just use WSGS as the kludge argument. The good news is that these CHOOSE calls impose almost no recalc overhead EXCEPT that Excel considers all the cells in the ranges in 3rd and subsequent arguments to CHOOSE as precedent cells for formulas referring to WSGS.

    Point remains: it's possible to hack cell dependencies in order to avoid needing to make UDFs volatile. Maybe not absolutely robustly, but often adequately robustly.

  36. #36
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hrlngrv View Post
    Your UDF doesn't include Application.Volatile, so it may not recalculate when you believe it would....
    I see this conversation has moved on somewhat however, I can only reiterate my earlier point - the attached, for me, operates devoid of the Volatile

    e.g. change values on other worksheets (as denoted) and the UDF (Worksheet1!G2) will recalculate.... at least it does on O365

    Now, the above is a) odd and b) odd... but certainly the case for me ... I don't have access to earlier versions, presently, so perhaps you could test the same, and revert findings?
    (I did do a quick google, at the time, to see if the UDF behaviour had changed in O365 but could find nothing)

    Like yourself, (and BMV), I would have expected the need for Application.Volatile given implicit precedents or, as you have suggested, a pseudo explicit precedent - either embedded as preemptive IF, or the UDF itself, to enforce the dirty flag)

    I don't disagree with any of your assertions re: 3d conditional calcs being poor performing etc, indeed the same was posted a few pages back, by various people.
    Attached Files Attached Files
    Last edited by XLent; 01-30-2020 at 05:34 AM. Reason: typo in narrative

  37. #37
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Index Match with Indirect and Named Range

    Update:

    Another has kindly tested the prior attachment on 2016 which confirms change in O365 behaviour.

    In short, in O365, it seems UDFs are treated as Volatile functions by default

    Further to the above, I tested a UDF with only explicit precedents and it still appears to behave in a volatile manner, e.g.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If, in A1, you enter =xyz(B1,C1) you will get current time

    if you enter values in any cells other than B1 / C1 (the explicit precedents) the Time value in A1 will change, i.e. it is Volatile.

    Whilst reassuring to know I was not going mad, this seems a bizarre change by MS.

    On an aside: the same person that tested the above also advised they had seen similar changes in Evaluate behaviour - so it would seem that some very notable changes are taking place 'under the hood' in O365.

    So, in summary, for OP (O365) the Application.Volatile call is not necessary but given it acts as Volatile it's (still) ill advised.

  38. #38
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    XLent.
    2013 - Application.Volatile (False) by default
    2016 - Application.Volatile (False) by default
    2019 - Application.Volatile (False) by default
    Excel 365 x64 personal 1912 - Application.Volatile (False) by default
    Last edited by BMV; 01-30-2020 at 01:18 PM.

  39. #39
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11

    Re: Index Match with Indirect and Named Range

    So I tried using a SUMPRODUCT formula and including Worksheet1:Worksheet5, but it gives me #NAME? error. I moved the formulas to Sheet1, where I tested BMV's two UDFs as well as XLent's UDF. I updated the other worksheets to show the extent of how I would like to use the formula. My spreadsheet is several times larger than this example and does not seem to work well with either of the UDFs.

    -BMV, I saw your other workbook (must've missed it earlier) but I need to go through the formula and try to apply it to my spreadsheet; but upon reading it - I feel like it might have a long refresh time as well.
    Attached Files Attached Files
    Last edited by hikari8; 01-30-2020 at 03:28 PM.

  40. #40
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hikari8 View Post
    . . . tried using a SUMPRODUCT formula and including Worksheet1:Worksheet5, but it gives me #NAME? error. . . .
    SUMPRODUCT can't handle 3D references. Or were you trying my formula in #26 but didn't change the row_crit and col_crit placeholders?

  41. #41
    Registered User
    Join Date
    01-22-2020
    Location
    New York
    MS-Off Ver
    Office 365 Version 1808
    Posts
    11
    Quote Originally Posted by hrlngrv View Post
    SUMPRODUCT can't handle 3D references. Or were you trying my formula in #26 but didn't change the row_crit and col_crit placeholders?
    I would like to use the formula in place of the sum3dx formula in each of the worksheets. I did use your formula as a base.

  42. #42
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by XLent View Post
    . . . the attached, for me, operates devoid of the Volatile . . .
    No it doesn't.

    Still using Excel from Office 365.

    Change Worksheet1!B2 to AC. The desired result in E2 and the formula using your UDF in G2 both change to 432. Why did yours change? Because it's a formula in Worksheet1 with a reference to $B$2, so to Worksheet1!B2, so the change in Worksheet1!B2 to AC triggered recalc of the formula in Worksheet1!G2.

    Now switch to Worksheet2. It's cell G9 had G5 = Worksheet1!B2 and B9 = Worksheet1!B3, so it should be included in the 3D sum's result. Subtract 1,000,000 from Worksheet2!G9. Now return to Worksheet1. The resired result in E2 now shows -999568. Unfortunately, G2 blissfully continues to show 432. Press [F9] as many times as you like, and G2 will still show 432.
    Last edited by hrlngrv; 01-30-2020 at 05:19 PM.

  43. #43
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by XLent View Post
    . . . Another has kindly tested the prior attachment on 2016 which confirms change in O365 behaviour.
    In which case that other has as little understanding of the problem as you seem to.

    In short, in O365, it seems UDFs are treated as Volatile functions by default . . .
    The only accurate categorizations for this claim are comprehensive misunderstanding or pure BS.

    ADDED: Re VBA's Time function, did you stop to consider that it itself might be volatile? Since Excel's worksheet function NOW() is volatile, it'd come as a complete shock that VBA's Time might also be volatile? FWIW, that's another trick for ad hoc volatility. Using your SUM3D as-is, add 0*NOW() to it, and, Voilà, the formula recalcs all the time.

    Try this instead.

    Please Login or Register  to view this content.
    Use them in cell formulas.

    A1: =BeatThisDeadHorseToABloodyPulp($E$1)
    A2: =BeatThisDeadHorseToAnEvenBloodierPulp($E$1)

    These should produce the same result. Do they?

    Now change the worksheet's name. Do they still produce the same result? Not on my system.

    However, enter the formula =NOW() in E1. A1 and A2 are once again the same. Change the worksheet name again. Are A1 and A2 still the same? This time both formulas depend on a cell containing a formula calling a volatile function. Dunno about your system, but on mine A1 doesn't change.

    Change x's argument type to Variant in both UDFs, then change the A1 and A2 formulas' arguments to COUNT($E$1). At this point, changing worksheet names changes both A1 and A2 so they remain the same and return the current worksheet name.

    Volatility is not a simple subject, and much as you may want to believe it's the new default, it ain't.
    Last edited by hrlngrv; 01-30-2020 at 05:00 PM.

  44. #44
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,262

    Re: Index Match with Indirect and Named Range

    Hi?
    I optimized sum2d function for better performance.
    Please Login or Register  to view this content.

  45. #45
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Index Match with Indirect and Named Range

    Quote Originally Posted by hrlngrv View Post
    ...change x's argument type to Variant in both UDFs, then change the A1 and A2 formulas' arguments to COUNT($E$1). At this point, changing worksheet names changes both A1 and A2 so they remain the same and return the current worksheet name.
    @hrlngrv, thanks for taking the time to step-through the above, and correct my prior assertion - it is appreciated !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Why does Index and Match Formula work for Named Range.. Except The Last Rows?
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-27-2018, 02:26 PM
  2. Named range in Index & Match
    By edward_glyver in forum Excel General
    Replies: 10
    Last Post: 04-29-2016, 08:11 AM
  3. [SOLVED] INDEX MATCH VLOOKUP Named Range
    By overbomb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-03-2015, 12:56 PM
  4. [SOLVED] Reference to named range to be used in index match formula!! Help Please!!
    By Optimum in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-24-2013, 06:54 AM
  5. Named range lookup using Index/Indirect/Match Function calls in Macro
    By sgopan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2013, 07:51 PM
  6. Cancel posting
    By Richard3 in forum Excel General
    Replies: 1
    Last Post: 02-24-2012, 12:45 PM
  7. Named range lookup using Index and match
    By Sirrob01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-01-2007, 11:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1