Previously I did a tutorial called "How To Extract A Dynamic List From A Data Range Based On A Criteria Without Filters In Excel". I received a question of how to modify the formula if names were added, especially at the top of the list. This tutorial addresses that question.
You can find the previous post here.
You can download the file here and follow along. When you get a preview, look for Download in the upper right hand corner.
To understand fully the process and benefits of this concept, please take a few minutes to watch the previous video from the link indicated above. In that video we used several similar formulas. The one we'll focus on in cell "E2" of the original worksheet is:
{=IFERROR[INDEX[$A$2:$A$26,SMALL[IF[$B$2:$B$26=$K$1,ROW[$A$2:$A$26]],
ROW[1:1]]-1,1],""]}
As you can see, it's basically an INDEX formula wrapped in an IFERROR statement. The challenge was to make the formulas in column "E" [and similar formulas in column "H"] more dynamic so if we added any entries to the main list, whether at the top, in the middle, or at the bottom, the formulas would properly extract the correct information. I created two methods to do this:
OPTION #1: OFFSET
I modified the formula in cell "E2" as follows:
{=IFERROR[INDEX[OFFSET[$A$1,1,0,COUNTA[$A:$A]-1,1],SMALL[IF[
OFFSET[$B$1,1,0,COUNTA[$A:$A]-1,1]=$K$1,ROW[OFFSET[$A$1,1,0,COUNTA[$A:$A]-1,1]]],
ROW[1:1]]-1,1],""]}
Basically, all I did was replace the static range for the INDEX, IF, and ROW formulas with OFFSET formulas. Let's take the array from the INDEX formula as an example. In the original formula, the array was:
$A$2:$A$26
In the new formula, we've modified it to be:
OFFSET[$A$1,1,0,COUNTA[$A:$A]-1,1]
Let's see how this works. I've used the OFFSET function in many of my tutorials. If you want to see many of the uses of this function, click on OFFSET in the right column under "Excel Tutorial Topics" and you'll see about a dozen different tutorials using the versatile function.
=OFFSET[reference,rows,cols,[height],[width]]
OFFSET basically says "give me an anchor point [reference] then from that point, tell me how many rows down and how many columns over from that point do you want to go. In many formulas in which you only need to reference a single cell, that's all you will need to use. There are two other optional arguments you may use if you want to define a range rather than a single cell, one to define how many rows high the range will be, the other how many columns wide.
So let's break down our OFFSET formula:
Reference: $A$1 this cell is our anchor point
Rows: 1 this is telling Excel to go down one row from cell A1, which is the start of the list of names
Cols: 0 this is telling Excel not to go over any columns. We want to have our range start at cell A2
[height]: COUNTA[$A:$A]-1 here we're telling Excel that we want the range to be as many rows high as there are items in column A, minus one to account for the header of that column
[width]: 1 we only want the list of names in the INDEX array, so we want the range to be only one column wide
Looking at the formula, you'll see that we used similar logic for the IF and ROW functions.
Take some time to get familiar with the OFFSET function and I think you'll find it an invaluable tool!
OPTION #2: TABLE
This option is quite simple and basic. Convert the main list to a table, then reference the table name and column name in the formula instead of either a static range or, as we did with Option #1, a formula like OFFSET.
In our example, I've named the table "Donors". So the formula in cell "E2" now looks like this:
{=IFERROR[INDEX[Donors[Guest],SMALL[IF[Donors[Attending]=$K$1,ROW[Donors[Guest]]],
ROW[1:1]]-1,1],""]}
Now, instead of the static range:
$A$2:$A$26
or the formula alternative:
OFFSET[$A$1,1,0,COUNTA[$A:$A]-1,1]
this [and the other ranges in the formula] can be replaced by the table reference, the example being:
Donors[Guest] ["Donor" being the table name, and "Guest" being the column header]
Tables are a phenomenal tool in Excel and one that you must become familiar with. There are so many benefits to using tables. If you are not familiar or comfortable with tables in Excel, please take a moment to see this tutorial on the benefits of tables over ranges here.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel click on the YouTube icon below!
Happy Excelling!