Excel Video 181 takes our INDEX and MATCH discussion one step further. Last time we used the MATCH function to calculate the row for INDEX to use. In this video, I’ll demonstrate how to use MATCH to provide both the row and column in an INDEX function. By using MATCH twice we can allow users to look anywhere in our data table for the information we need. Notice how I use a data selection box for one MATCH function and use wildcards for the other MATCH function. You can mix and MATCH to get exactly the lookup performance you need.
INDEX and MATCH functions look complicated, but once you get the hang of it, they aren’t hard to use and provide a lot of flexibility. Stay tuned. Next time we’ll look at how to find data in multiple tables using INDEX.