Using the INDEX-MATCH formula and combining the data to verify the drop-down menu, you can return values ??based on two conditions. This method is a bit advanced and requires you to temporarily put down the work at hand and concentrate. Let's get started...

Key Points

• Combining INDEX and MATCH for powerful searches - Using INDEX MATCH is more flexible than VLOOKUP, especially when dealing with non-adjacent columns .
• Handle multiple conditions using array formulas - multiple conditions can be used in MATCH by combining conditions (e.g., using & or * ) to find the exact row that satisfies both conditions.
• Integrate data verification for dynamic input - Add a data verification drop-down list , allowing users to dynamically select conditions to ensure that the inputs found are clean and controlled.
• Avoid VLOOKUP limitations - INDEX MATCH can be found from left to right and right to left , and there is no need to find a column on the left, like VLOOKUP does.
• Efficient and scalable solution - This approach is faster, more reliable, and easier to maintain , especially when dealing with large data sets or changing table structures.

Table of contents

Quick Overview

What does it do?

Search for the row position of the value/text in a column (using the MATCH function), and return the value/text from another column at the same row position (using the INDEX function), whether the column is on the left or right.

Formula decomposition:

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))

This means:

=INDEX (return this value/text, MATCH (from the line position of this value/text))

First, we need to convert the data into an Excel table by pressing Ctrl T.

See a tutorial on how to convert to Excel tables

We then create a drop-down menu for the Sales Representative column and another drop-down menu for the Unit/Sales/Average Sales column names.

See the tutorial on how to insert the drop-down menu

After completing the above steps, we need to create our formula.

How to use INDEX MATCH and data validation for dual condition search

Step 1: We need to nest an INDIRECT function in the INDEX function and reference the indicator cell name (H14) and our table name (Table1):

=INDEX(INDIRECT("Table1["&H14&"]"),

This will provide us with dynamic column names in Excel tables.

Step 2: We need to find our sales rep in the sales rep list:

=INDEX(INDIRECT("Table1["&H14&"]"),

MATCH(G14,Table1[SALES REP],0))

By combining these formulas, we can select two conditions (sales representative and metric name) to return the corresponding value.

Frequently Asked Questions

What are the advantages of using INDEX MATCH over VLOOKUP?
INDEX MATCH is more flexible , allows searches in any direction, and is suitable for non-adjacent columns, unlike VLOOKUP.

How to apply multiple conditions in INDEX MATCH?
You can use array formulas in the MATCH function in combination with conditions, for example: MATCH(1, (A2:A10=E1)*(B2:B10=F1), 0) This allows you to check two conditions at the same time.

Why use data verification in INDEX MATCH?
Data Verification creates a drop-down list that allows users to select valid options, reduce errors, and make lookups dynamic and user-friendly.

Do I need to use Ctrl Shift Enter to make the formula work?
In older versions of Excel, yes. However, in Excel 365 and Excel 2019 , array formulas like this can work without special key presses .

Can I extend this method to more than two conditions?
sure! Just add more conditions in the MATCH section: (Criteria1=Cell1)*(Criteria2=Cell2)*(Criteria3=Cell3) and adjust the number of conditions as needed.

The above is the detailed content of Index Match 2 Criteria with Data Validation. For more information, please follow other related articles on the PHP Chinese website!