What should I do in order to solve an inconsistent reference error when using INDEX/MATCH?

[numbernoodle]

Hello everyone, I have an issue with an INDEX/MATCH function, and I would very much appreciate your help! For context:

• In the "CO" tab, I have one table with data for 11 different neighborhoods (names are in row 6) for all years from 2001 to 2033 (years are in column C). Starting from 2027 (cell C33), all data is based on growth estimates. The growth estimates are percentages, so "data from previous year * (1 + growth estimate)" is how you would calculate the data for each of these years.
• In the "AO" tab, I have two tables with DIFFERENT VERSIONS of growth estimates for all 11 different districts for 2027-2033.
• The first table has the same names for the 11 different neighborhoods as the CO tab, in cells P14:Z14, and the same years as the CO tab (2027-2033) in cells O15:O21. The array where the growth estimates are located is P15:Z21.
• The second table has the same names for the 11 different neighborhoods as the CO tab, in cells P23:Z23, and the same years as the CO tab (2027-2033) in cells O24:O30. The array where the growth estimates are located is P24:Z30.
• The value of A2 in the CO tab determines which estimate is used, if at all. It can be Green, Blue or Red. IF the value of A2 is "Blue", the growth estimates from the first table for a particular neighborhood name and a particular year must be used. If the value of A2 is "Red", then the growth estimates from the second table must be used. I've made the following formula: = M32*(1+IF($A$2="Green",0, IF($A$2="Blue", INDEX(AO!$P$15:$Z$21, MATCH(M$6, AO!$P$14:$Z$14), MATCH($C33, AO!$O$15:$O$21)),INDEX(AO!$P$24:$Z$30, MATCH(M$6,AO!$P$23:$Z$23), MATCH($C33,AO!$O$24:$O$30))))) The formula works properly for 9 neighborhoods, but when I get to the 10th neighborhood (the 11th neighborhood doesn't work either), which has a name that is 7 letters long, it stops working and displays a #REF error. When I evaluate the formula, everything seems to work as it should, since 10 is shown to be the column number (10th neighborhood) and 1 the row number (2027, first year in the array). Even so, the corresponding value is not shown in the evaluation or the actual tab. I've already checked the spacing, data formatting, etc. I've also tried using XLOOKUP or FILTER but these formulas return #VALUE errors for all neighborhoods. My excel version is 2404. Could you please help me figure out if this is a software bug or something that can be fixed? Thank you so much.

 

[numbernoodle]

Hello everyone, I have an issue with an INDEX/MATCH function, and I would very much appreciate your help! For context:

• In the "CO" tab, I have one table with data for 11 different neighborhoods (names are in row 6) for all years from 2001 to 2033 (years are in column C). Starting from 2027 (cell C33), all data is based on growth estimates. The growth estimates are percentages, so "data from previous year * (1 + growth estimate)" is how you would calculate the data for each of these years.
• In the "AO" tab, I have two tables with DIFFERENT VERSIONS of growth estimates for all 11 different districts for 2027-2033.
• The first table has the same names for the 11 different neighborhoods as the CO tab, in cells P14:Z14, and the same years as the CO tab (2027-2033) in cells O15:O21. The array where the growth estimates are located is P15:Z21.
• The second table has the same names for the 11 different neighborhoods as the CO tab, in cells P23:Z23, and the same years as the CO tab (2027-2033) in cells O24:O30. The array where the growth estimates are located is P24:Z30.
• The value of A2 in the CO tab determines which estimate is used, if at all. It can be Green, Blue or Red. IF the value of A2 is "Blue", the growth estimates from the first table for a particular neighborhood name and a particular year must be used. If the value of A2 is "Red", then the growth estimates from the second table must be used. I've made the following formula: = M32*(1+IF($A$2="Green",0, IF($A$2="Blue", INDEX(AO!$P$15:$Z$21, MATCH(M$6, AO!$P$14:$Z$14), MATCH($C33, AO!$O$15:$O$21)),INDEX(AO!$P$24:$Z$30, MATCH(M$6,AO!$P$23:$Z$23), MATCH($C33,AO!$O$24:$O$30))))) The formula works properly for 9 neighborhoods, but when I get to the 10th neighborhood (the 11th neighborhood doesn't work either), which has a name that is 7 letters long, it stops working and displays a #REF error. When I evaluate the formula, everything seems to work as it should, since 10 is shown to be the column number (10th neighborhood) and 1 the row number (2027, first year in the array). Even so, the corresponding value is not shown in the evaluation or the actual tab. I've already checked the spacing, data formatting, etc. I've also tried using XLOOKUP or FILTER but these formulas return #VALUE errors for all neighborhoods. My excel version is 2404. Could you please help me figure out if this is a software bug or something that can be fixed? Thank you so much.

SOLVED! Do not reply further, thank you!