Spreadsheet: 824176d1680352198-getpivottable-function-not-working-with-grouped-range-of-cells-getpivotdata-sample-2-years-1-
Sheet: Sheet3

Average of CF |  |  | Month
Year | Nameplate | NG ID | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | Grand Total
2022 | 0.007-0.507 |  | 0.06638378919397196 | 0.08480331464676408 | 0.1265874116403126 | 0.1739458707379848 | 0.2072949599073457 | 0.1987309562078009 | 0.1963506170448174 | 0.1663374263446995 | 0.1358403343702078 | 0.1346134313066481 | 0.09554301339780594 |  | 0.1474999897088082
 | 0.507-1.007 |  | 0.04322356037187687 | 0.07886292079975213 | 0.124312206499986 | 0.1731050529923973 | 0.2233110484939112 | 0.2247704253332122 | 0.2381518207267762 | 0.2004648984197883 | 0.1355763992499986 | 0.120251060043765 | 0.06933051409070085 |  | 0.1468741790454609
 | 1.007-1.507 |  | 0.03824268048174241 | 0.08107268290457946 | 0.138692053813794 | 0.1964421917127856 | 0.2399381657488659 | 0.2693464417482617 | 0.2606246595439835 | 0.2046038379406474 | 0.1655348316068303 | 0.1621345247577327 | 0.08792393926493543 |  | 0.1696783596409724
 | 1.507-2.007 |  | 0.06914071150691385 | 0.1051907387007833 | 0.1639426802103214 | 0.2068410505389672 | 0.2451393551191938 | 0.2665269414227747 | 0.2795127680712358 | 0.2314810381630658 | 0.1823167259327974 | 0.174325542289252 | 0.1156341523702635 |  | 0.1869446515015335
 | 2.007-2.507 |  | 0.05913978494623656 | 0.1065476190476191 | 0.2006720430107527 | 0.2552777777777778 | 0.3100806451612903 | 0.3158333333333334 | 0.3048387096774194 | 0.2456989247311828 | 0.2306944444444445 | 0.2073924731182796 | 0.1270833333333333 |  | 0.2184627109158242
 | 2.507-3.007 |  | 0.07794668973756849 | 0.1114931125706988 | 0.1309609583038393 | 0.1583246255660049 | 0.2094990505724655 | 0.2352875014512945 | 0.2595806507792047 | 0.2114646183750745 | 0.1448124512779685 | 0.1560856459076704 | 0.1067766747939162 |  | 0.1650480641558691
 | 3.007-3.507 |  | 0.05940135386442225 | 0.1042068402589676 | 0.1372584928629174 | 0.2069370360175602 | 0.2704319801718348 | 0.2767402903472743 | 0.2718008556422216 | 0.2587841699776337 | 0.1883613741097018 | 0.171934636445697 | 0.1059012923523946 |  | 0.1934109070155457
 | 3.507-4.007 |  | 0.06227435014719936 | 0.1104565728673573 | 0.1652415946827552 | 0.2138362122076191 | 0.2774246400542654 | 0.2836393260938589 | 0.2792127598934595 | 0.2143906563608337 | 0.176089474108725 | 0.1880605191782932 | 0.1149127009541493 |  | 0.1873765641195282
 | 4.007-4.507 |  | 0.06897378805980957 | 0.125253026294693 | 0.1456070562253358 | 0.2001964085297419 | 0.2568199920350458 | 0.3176229891507669 | 0.3282669888852685 | 0.2798685782556751 | 0.2086162551440329 | 0.170590311719344 | 0.1172254956977179 |  | 0.2017309899997665
 | 4.507-5.007 |  | 0.06662671850686976 | 0.1084145960900793 | 0.1322126060105407 | 0.1889004540562236 | 0.2345632583670269 | 0.2337808285453695 | 0.2348749057765066 | 0.2279360727651453 | 0.1815422710371717 | 0.1670407032848747 | 0.1067685755567602 |  | 0.1749181552105743
2022 Total |  |  | 0.06415408943976916 | 0.0949680651589699 | 0.1342066719584799 | 0.1852415919181869 | 0.2264738378963034 | 0.2263967911367828 | 0.2266210656245895 | 0.1995528496345779 | 0.1589141820351272 | 0.1525098076380352 | 0.1012063709301215 |  | 0.164015559028752
2021 | 0.007-0.507 |  | 0.05958583500492426 | 0.07461985539788941 | 0.1544333728525265 | 0.1628879998884339 | 0.1890921724234621 | 0.2007906969262754 | 0.1555083728109961 | 0.1612426228632766 | 0.149045880596198 | 0.116592976722416 | 0.1035386824126576 | 0.0781655424564086 | 0.1394411958491446
 | 0.507-1.007 |  | 0.03999595328939762 | 0.07628881230854916 | 0.1912028162671681 | 0.1805229097235323 | 0.1861761137244004 | 0.2014565735325969 | 0.1566606506921329 | 0.1740243607245269 | 0.1741036806472573 | 0.0854627161710883 | 0.0756122185192172 | 0.0339496272187 | 0.130571421486353
 | 1.007-1.507 |  | 0.02779955694942254 | 0.0489700689503132 | 0.1984691924497727 | 0.201732452639421 | 0.2163606210261058 | 0.2590062703987512 | 0.1934875556173526 | 0.177464243535341 | 0.1845186781609195 | 0.1254600447685357 | 0.09094339789981552 | 0.05281116535519575 | 0.1460243494476149
 | 1.507-2.007 |  | 0.05627121347887477 | 0.07593375172740252 | 0.2180229932022047 | 0.2302351641414141 | 0.2448490710139456 | 0.2617253888087221 | 0.2197494094167481 | 0.2540762123927446 | 0.2156779601571268 | 0.1256259040309219 | 0.1034680936347603 | 0.05430553175237927 | 0.1676937305789416
 | 2.007-2.507 |  | 0.04516129032258064 | 0.06458333333333333 | 0.221236559139785 | 0.1713888888888889 | 0.2897849462365591 | 0.2975 | 0.167741935483871 | 0.2277777777777778 | 0.2124074074074074 | 0.1435483870967742 | 0.1216666666666667 | 0.0450268817204301 | 0.1606078737426292
 | 2.507-3.007 |  | 0.05114268379006977 | 0.06809318555008209 | 0.2216435770384602 | 0.2123079453539224 | 0.2601543802878619 | 0.2771556561786447 | 0.146856495994427 | 0.1926509252705026 | 0.2122276403885599 | 0.1390789616596068 | 0.1113060683462982 | 0.0558935667389505 | 0.1668076913155621
 | 3.007-3.507 |  | 0.06317204301075269 | 0.06996166805280105 | 0.2184641282672912 | 0.2432851846082731 | 0.2757822434032716 | 0.2893602170776083 | 0.2567820061714244 | 0.2766802140986017 | 0.1997719209148124 | 0.1274763260682934 | 0.1081808100331988 | 0.05519552452187034 | 0.1835478430799048
 | 3.507-4.007 |  | 0.06115591397849462 | 0.06696428571428571 | 0.1701948924731183 | 0.18359375 | 0.2577284946236559 | 0.216412037037037 | 0.1929676730079956 | 0.1897677143644886 | 0.1483365978157645 | 0.1065618968844775 | 0.1082724952516619 | 0.05357002742602734 | 0.1429449564175032
 | 4.007-4.507 |  | 0.05895650048875855 | 0.1156655844155844 | 0.2223851417399804 | 0.2231691919191919 | 0.2620967741935484 | 0.2705176767676767 |  | 0.04582111436950146 | 0.1960227272727273 | 0.1481549364613881 | 0.1133207070707071 | 0.02502172260236776 | 0.1331673478850898
 | 4.507-5.007 |  | 0.05308554039453869 | 0.09399179382732013 | 0.2157493927018545 | 0.201415726812168 | 0.2533363693187442 | 0.2581086411398266 | 0.2059198123971522 | 0.2190837042429204 | 0.1762972317530881 | 0.1142900427779235 | 0.09989149664113903 | 0.04582995445417075 | 0.1568260588819101
2021 Total |  |  | 0.05564258610956861 | 0.07754049091406387 | 0.1766395951431012 | 0.1803547413958908 | 0.2123038853540069 | 0.2231569261662089 | 0.1739157763682389 | 0.1838944010285524 | 0.1641252352125469 | 0.1169864070484225 | 0.1022426750568549 | 0.06194622356280344 | 0.1470973503394354
Grand Total |  |  | 0.06081807897701659 | 0.08854087465730245 | 0.1511307858298029 | 0.1832619329247353 | 0.2205860459812218 | 0.2250637676747755 | 0.2047302444255232 | 0.19319160488838 | 0.1610810230247175 | 0.137993900290503 | 0.101627454928847 | 0.06194622356280344 | 0.1567524399817184
 |  | GETPIVOTDATA(data_field, pivot_table, [field1, [NAME], [NAME], item2], ...)
Year | lookup [NAME] | Find result | MONTH
2022 | 0.007 |  | 7
2022 | 0.507 |  | 3
2022 | 4.007 |  | 9
2021 | 3.7 |  | 6 | ? Can function be changed to look up year 1st?
2021 | 2.3 |  | 4 | ? Can function be changed to look up year 1st?
2021 | 2.507 |  | 5 | ? Can function be changed to look up year 1st?
GetPivottable function not working with grouped range of cells
I have a pivot table set up with field1 rows grouped into 10 buckets that have a low and high range separated by 0.5.
I would like to use getpivotdata function to look up data values using the item1 value that will fall in between the low and high range of field1 value called "nameplate"
In the sample, the getpivotdata functions works when [NAME] is equal to the field1 low or high value but does not work when the value falls in between the two values.
Any recommended solution would be appreciated.
Thank you
 |  |  |  |  |  |  | 0.507
 |  |  |  |  |  |  | 1.007
 |  |  |  |  |  |  | 1.507
 |  |  |  |  |  |  | 2.007
 |  |  |  |  |  |  | 2.507
 |  |  |  |  |  |  | 3.007
 |  |  |  |  |  |  | 3.507
 |  |  |  |  |  |  | 4.007
 |  |  |  |  |  |  | 4.507
 |  |  |  |  |  |  | 5.007