Excel Video 180 combines INDEX and MATCH to create a powerful, flexible lookup tool. Now that we’ve seen both INDEX and MATCH in the last two Excel Videos, the trick is to combine the function to create a lookup tool. Remember INDEX asks for the area to look in and then the row and column for the reference. In this example, we know that we’ll always look in column 2 for the NPI. By using MATCH to tell INDEX which row to look in, we can create a formula that lets us look up the NPI for any provider’s initials we want.
INDEX and MATCH are two functions and are a more complicated approach than VLOOKUP. If you’re new to VLOOKUP, I’d master VLOOKUP first. Once you’re familiar with VLOOKUP, get familiar with combining INDEX and MATCH. It may take more time at first, but once you’ve got it down, you have another powerful lookup tool in your Excel tool chest that’s more flexible than VLOOKUP.
Stay tuned. Next time we’ll do INDEX and MATCH x2. It’s like INDEX and MATCH b.i.d. We’ll use MATCH to look up both the row and column we need for INDEX so that an end user can look up whatever they need to.
