{"id":12197,"date":"2022-02-11T12:43:07","date_gmt":"2022-02-11T12:43:07","guid":{"rendered":"https:\/\/mailinvest.blog\/index.php\/2022\/02\/11\/how-to-use-excels-index-match-formula-with-multiple-criteria\/"},"modified":"2022-02-11T12:43:07","modified_gmt":"2022-02-11T12:43:07","slug":"how-to-use-excels-index-match-formula-with-multiple-criteria","status":"publish","type":"post","link":"https:\/\/mailinvest.blog\/index.php\/2022\/02\/11\/how-to-use-excels-index-match-formula-with-multiple-criteria\/","title":{"rendered":"How to Use Excel’s INDEX\/MATCH Formula With Multiple Criteria"},"content":{"rendered":"

<\/a>\n<\/p>\n

\n

Excel<\/a> is an incredibly powerful software \u2013 if you know how to leverage it. With so many functions and formula options, there\u2019s something new to learn every day.<\/p>\n

<\/p>\n

The INDEX\/MATCH formula can help you find data points quickly without having to manually search for them and risk making mistakes.<\/p>\n

\"Download<\/a><\/span><\/span><\/p>\n

Let\u2019s dive into how that formula works and review some helpful use cases.<\/p>\n

Understanding INDEX and MATCH Functions Individually<\/h2>\n

Before you can understand how to use the INDEX and match formula, it\u2019s valuable to know how each function works on its own. That will offer some clarity on how both work together once combined.<\/p>\n

The INDEX function returns a value or the reference to a value within a table or range based on the rows and columns you specify. Think of this function as a GPS \u2013 it helps you find data within a document but first, you need to narrow down the search area using rows and columns.<\/p>\n

The MATCH function identifies a specific item in a range of cells then returns the relative position of that item in the range or the exact match.<\/p>\n

For instance, say the range A1:A4 contains the values 15, 28, 49, 90. You want to know how the number “49” is relative to all values within the range. You would write the formula =MATCH(49,A1:A4,0) and it would return the number 3 because it\u2019s the third number in the range. The 0 in the formula represents “exact match.”<\/p>\n

Now that we\u2019ve got the basics out of the way, let\u2019s get into how to combine the formula and use it for multiple criteria.<\/p>\n

How to Use the INDEX and MATCH Formula with Multiple Criteria<\/h2>\n

The formula for the INDEX\/MATCH formula is as follows:\"excel<\/p>\n

Here\u2019s how each function works together: Match finds a value and gives you its location. It then feeds that information to the INDEX function, which turns that information into a result.<\/p>\n

To see it in action, let\u2019s use an example.<\/p>\n

\"excel<\/p>\n

This Excel sheet features a marketing budget for two categories: Events and company swag gifts. There are four purposes: Public relations (PR), celebration, team outing, and rebranding. The sheet also includes the defined budget and the actual expense for each category.<\/p>\n

This is where the INDEX and MATCH formula comes in handy when using it for multiple criteria. You can quickly find the answer(s) you\u2019re looking for and limit mistakes that would happen when searching manually.<\/p>\n

Say you want to know the variance for an event that had a purpose of celebration with a budget of 10,000 \u2013 here\u2019s how you\u2019d do it.<\/p>\n

\"excel<\/p>\n

First, take note of the row numbers and columns. The answer you\u2019re looking for will go in I8. Here\u2019s how the formula will look:<\/p>\n

\"excel<\/p>\n

Let\u2019s break down how you get there.<\/p>\n

1. Create a separate section to write out your criteria.<\/h3>\n

\"excelThe first step in this process is by listing out your criteria and the figure you’re looking for somewhere in your sheet. You’ll need this section later to create your formula.<\/p>\n

2. Start with the INDEX.<\/h3>\n

\"how<\/p>\n

The formula starts with your GPS, which is the INDEX function. You\u2019re looking for the variance, so you select rows E4 through E9, as that is where the answer will be.<\/p>\n

3. Add your ranges.<\/h3>\n

The more columns you have, the more ranges you\u2019ll need to add to narrow down your results.<\/p>\n

As a reminder, you\u2019re looking for the variance for an event<\/strong> that had a $10,000<\/strong> budget and had a purpose of celebration<\/strong>. This means that you\u2019ll have to tell Excel which rows hold the<\/p>\n

\"how<\/p>\n

Starting with the “event,” criteria, you find it first in I4., with its range located in column A between rows 4 and 9.<\/p>\n

Follow the same process for “celebration” \u2013 it\u2019s in I5 and its range is B4 and B9. Lastly, the “$10,000” is in I6, with a range of C4 through C9.<\/p>\n

The last step here is to add 0, which means you\u2019re looking for an exact match.<\/p>\n

That\u2019s how you end up with this final formula:\"excel<\/p>\n

4. Run the formula.<\/h3>\n

\"excel<\/p>\n

Because this is an array formula, you must press Ctrl+Shift+Enter to get the right results, unless you are using Excel 365.<\/p>\n

There you have it!<\/p>\n

\"excel<\/a><\/span><\/span><\/p>\n<\/div>\n