From the course: Cert Prep: Excel 2016 Microsoft Office Expert (77-728)

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Use the MATCH and INDEX functions

Use the MATCH and INDEX functions

From the course: Cert Prep: Excel 2016 Microsoft Office Expert (77-728)

Start my 1-month free trial

Use the MATCH and INDEX functions

“

- [Instructor] Very often we need to use Excel's functions to look up a specific value for us, and most of the time we'll use HLOOKUP or VLOOKUP and those seem to work just fine. But the lookup functions can only search one row or one column of information. In this video I'm going to show you how to use the MATCH and INDEX functions together to search through an array to find the value that we're looking for. The MATCH function will look in a column of data and return the row number of the specified value. Once we know the row number, we can use the INDEX function to find our unknown value. Now I have the 04_04 exercise file open so let's get started. Here's the information that we're looking for. In H2 we have Catering Dinners and we're looking for the amount of sales in 2016 for quarter four. We'll be using this array to find the MATCH that will return the row number that the Catering Dinner record is on. So let's go ahead and start with MATCH. I'll type an equal sign and the word…

Contents