I am trying to build out a new process using Smartsheet and was just recommended by Laura Adams to consider using Index & Match instead of VLookup because it requires less resources, makes it easier to reproduce, and is more secure.
How to build an Index-Match formula
Example
Let’s say you had a simple sheet that listed the faculty contact for any given course. In another sheet, you would like to pull that information in and you can as long as you have a cell with the same course information. This allows you to have a number of smaller source sheets that contain useful information that needs to be referenced in a number of other sheets.
You would set up your index-match function by:
- identifying (referencing) the column in the source sheet with the names of the faculty
- identifying the column in the destination sheet that contains the course information for each of the faculty
- identifying the column in the source sheet that contains the course information you are matching to
I’ve created some public sheets which anyone should be able to view to see this very simple example of index-match at work:
It is easy to get confused what gets referenced where in the formula.
Laura’s example above includes an IFERROR function that helps to turn “#NO MATCH” errors into either blanks or some other more useful, human-friendly message.
Some additional resources:
- YouTube: Taking Formulas to the Next Level (Advanced)
- @row – replaces a row number for a cell reference for the row of the formula (video example)
Don’t get frustrated because it’s easy to mess up the syntax. SmartSheet tries to help you, but sometimes add additional {} that can throw things off. Sometimes, it’s easier to rebuild a formula from scratch.
On a separate note, I was able to get the Chair of a Course Committee to show up because SmartSheet pulls the first INDEX listed which in our case was the Chair. So, as long as that doesn’t change, we’ll continue to get the data that we need. The data is structured in such a way that we have a row for every committee member that includes a column for the course number and the membership (which isn’t really needed because of the default functionality mentioned above).
Using this info again to customize a series of sheets for a Smartsheet Dashboard for faculty leaders to help with their decision making process related to Dev/Revs (new course developments / course revision projects). How do I do this again 😛 ?