There are two tricks to watch for in Excel Video 182. First, we’ll use the fourth section of the INDEX function so we can look in multiple areas in our spreadsheet for data. To look in multiple areas, first define each area in the first parameter of the INDEX function. Put the list of areas in parentheses and separate each area with commas. Then in the fourth parameter of the INDEX function, tell Excel which area you want to look in. Being able to look in multiple areas with one function is another way that INDEX is more powerful than VLOOKUP.
The second trick to watch for is that the COLUMN trick I showed you in Excel Video 174 works in INDEX as well. Remember that COLUMN simply figures out what column number you’re in. The reason to use COLUMN is so that you can copy an INDEX function and have the INDEX references update for you. In this example, COLUMN is giving us an answer that’s one higher than the area we want to reference, so I simply subtract 1 from the COLUMN to get the area I need. With that change and the appropriate absolute cell references, the INDEX function can be copied throughout my spreadsheet.
Stay tuned. In Excel Video 183 I’ll show you another way that INDEX might help you.
