The following exercises use the Purchase Orders database. Use MATCH and/or INDEX

Question

The following exercises use the Purchase Orders database. Use MATCH and/or INDEX functions to find the following:

The row numbers corresponding to the first and last instance of item number 1369 in column C (be sure column C is sorted by order number).

The order cost associated with the first instance of item 1369 that you identified in part (a).

The total cost of all orders for item 1369.Use the answers to parts (a) and (b) along with the SUM function to do this. In other words, you should use the appropriate INDEX and MATCH functions within the SUM function to find the answer.Validate your results by applying the SUM function directly to the data in column G.

Explanation / Answer

The MATCH function
The MATCH function returns the position of a cell within an array by matching against a criteria string. The formula looks like this:

=MATCH(lookup_value, lookup_range, match_type)

So, how do we combine INDEX and MATCH to replace VLOOKUP? We feed the MATCH function into the row_or_column argument of the INDEX function like so:
=INDEX(range, MATCH(lookup_value, lookup_range, match_type))
et's take a closer look at what's going on here. First, we call INDEX on a range of numbers from which we would like to look up a given value. Then, we use MATCH to tell Excel how many cells it should count into INDEX's range, based on a given value matched against a separate array.

That's a lot to digest, so let's take a look at an example to make things simpler. The following spreadsheet shows SnackWorld production by month. Let's say we want to look up how many Cookies were produced in March using the following table. We could do this easily using VLOOKUP, but let's try it with INDEX MATCH instead:

Why INDEX MATCH is better than VLOOKUP
After all this, you may be wondering why we even bother using INDEX MATCH. Isn't VLOOKUP just as good?

Not quite. Here are a few reasons you might want to use INDEX MATCH instead:

You don't have to count. With INDEX MATCH, there's no more worrying about counting to figure out which column you need to pull from.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.