Excel/9x9 grid


Hi Jerry,

I saw your solution for the 9x9 grid problem. I could not replicate or modify your solution to apply to mine. It seems anything I do outside the defined boxes does not work.
Can you tell me how you did it please?

The file can be downloaded from here:


Ignore the fact that it is setup as a 9x9 grid.  Just focus on one section.  Let's look at E1:E5, the "New or Emerging Leader" section.

E1 has the text we are matching in column B.  We are looking for the first few cells that have that value.

E2 has the first ARRAY formula for this section.  It INDEXes the used range in column A, A1:A118, then it uses a SMALL(IF()) test to make a collection of all the ROW numbers that match the column B value we are searching for.

In E2, the final trick is found in the ROWS() function at the end, this is filling the second parameter for us in the SMALL() function.  In E2 that reads ROWS($A$1:$A1), which resolves to "1" because there is only 1 row in that range.  But when that formula is copied down, in E2 it now reads ROWS($A$1:$A2) and the resolves to "2" because there are two rows in that range.

In this way the same formula in E2 copied down creates the same ARRAY in the SMALL(IF()) function, but it pulls a different "row" answer for each cell.

Once we have the E2 formula created, the array formula is confirmed by pressing CTRL-SHIFT-ENTER.  This activates the array and you will see curly braces {} appear around the formula to indicate the array is active.

Then you copy E2 down through E5 and the results for that section appear.  You could make the section larger by simply copying down further.

Now this process is repeated in the next section down.  Interestingly, the formula in E7 is identical to the initial formula in E2, except for one reference in the IF(), it refers to the text in E6 instead of E1.  In this way the same initial formula with that one tweak gives us different results for each section.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.