Last time, I went over a Sharepoint feature that’s saved me a lot of time and effort. At ArgonDigital,, as much as we use Sharepoint, we use Excel even more. Not as much for calculations, but for storing and sorting requirements and associated data, such as features. When we do this, the usual pros and cons of Excel are even more exaggerated: it’s really easy to store everything quickly, and it’s really easy to make a huge mess of all of your data.
One horror story came about when some requirements that had already been numbered were rearranged. Of course, rearranging them put those numbers out of order, and somebody else decided to re-number everything, which created a huge problem: no traceability. Any documentation or correspondence that referred to BR5, for example, would now be useless since that requirement was changed to BR10. Everything had to be remapped, and the team spent hours searching spreadsheets and re-mapping all of the requirements to their original designations.
When I heard this story, I figured there must be an easier way, and ended up finding a fantastic use of Excel functions INDEX and MATCH. I’ve since used this trick at least once a week, because we’re always getting files with different ordering, or selections from a large list, and we want to map comments or notes to the requirements in our working files.
First, you’ll use the INDEX function. This returns a position on a list, e.g. the 5th row or 18th row down. The first input, “array,” is the list of values you’re searching in. In this example, we have a list of people (Megan, Kell, and Matt) and want to know which numbers are associated with them in a different, larger list. So our “array” will be the column of numbers, $B$1:$B$10. We have to use $ to fix these references, because no matter where we use this formula, we’ll always be referring to the same list.
The next input is a little tricky, because we need to know what row the function should pull from. To figure that out, we use the MATCH function. MATCH searches a different list for whatever we tell it to look for, and returns the row that it’s in. So in our example, we want to search for whatever we’ve typed in cell A14, and we want to look in the list that’s in $A$1:$A$10. Remember to use the $, since we’ll always be referring to the same list. We also set the last input, “match type,” to 0. That just means we want to look for an exact match with our name; we don’t want “Meg” to return a match, for example. That’s everything for the MATCH function, so close the parentheses.
That whole MATCH function was only the second input within the INDEX function, so we have one more optional input: “column number.” In our example we only have one column, but this can be used to specify the 2nd, 3rd, or any column within your selection. This is useful if you want to drag your formula across multiple columns to import several pieces of data, such as L1, L2, and L3 process flows, without changing the cell formulas. We don’t need it for now, though, so just close the parentheses and drag the formula into the rest of the cells.
Voila! Just like that, we’ve returned the values we needed without having to manually search the list for names. You can probably see how this is helpful when you’re dealing with a list of 500 requirements. Also, if we’d searched for a name not on the list, such as “Tony,” we would know it was missing because Excel would return an error; either our list is incomplete, or we’re searching for the wrong thing.
Some of you may have noticed similarities between INDEX/MATCH and other Excel functions, notably VLOOKUP. Those functions do provide similar results, but are much less flexible: you break your function by inserting columns, cannot search for values to the left of your table, and several other issues detailed here. However, I’m a big fan of keeping things simple – if you use a method that works for you and avoids mistakes, stick with it… and share it with the rest of us! – How do you manage requirements in Excel?
