Excel Video 178 introduces INDEX, a more flexible lookup function than VLOOKUP or HLOOKUP. One of the shortcomings of VLOOKUP, for example, is that the data you’re looking for has to be in the far left column of the range you’re looking in. (No, you can’t enter a negative number for column reference to make VLOOKUP look at prior columns.) Combining INDEX with MATCH is a powerful way to look up a variety of information.
I’ll start Excel Video 178 by showing you a combined INDEX and MATCH formula and then we’ll start working on the INDEX function. The syntax for INDEX is:
=INDEX(reference area, row number, column number, area number)
If there’s only one row in your reference area, you don’t need to provide the row number. Similarly, if there’s only one column or area in your reference area, you don’t need to provide the column or area number. You DO need to leave the comma so Excel can keep track of which variable you’re providing.
We’ll worry about multiple areas in INDEX functions later. For now, look how easy it is to enter a row and/or column number and have INDEX find what you’re looking for. You can look things up vertically (like VLOOKUP) and horizontally (like HLOOKUP) at the same time. The trick is that instead of entering row 6 column 2, we’ll use MATCH to look up the row and/or column numbers for us. We’ll work on MATCH next, and then put INDEX and MATCH together. Stay tuned.
