Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How to Create Thermometer Chart in Excel
  • Move chart to a separate worksheet in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How To Load Analysis ToolPak in Excel

References

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in numeric data in Excel
  • How to use Excel INDIRECT Function
  • Convert text string to valid reference in Excel using Indirect function
  • How to use Excel ROWS Function

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation specific characters only
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in next 30 days
  • Excel Data validation require unique number

INDEX and MATCH with multiple criteria in Excel

by

This tutorial shows how to calculate INDEX and MATCH with multiple criteria in Excel using the example below;

Formula

{=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))}

Explanation

To lookup values with INDEX and MATCH, using multiple criteria, you can use an array formula. In the example shown, the formula in H8 is:

{=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}

Note: this is an array formula, and must be entered with control + shift + enter.

How this formula works

Normally, and INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on a given criteria. Without concatenating values in a helper column, or in the formula itself, there’s no way to supply more than one criteria.

Worked Example:   How to get last column number in range in Excel

This formula works around this limitation by using boolean logic to create an array of ones and zeros to represent rows matching all 3 criteria, then using MATCH to match the first 1 found.

The temporary array of ones and zeros is based on this snippet:

(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

Here we compare the item H5 against all items, the size in H6 against all sizes, and the color in H7 against all colors. the initial result looks like this:

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}*{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

The multiplication operation transforms the TRUE FALSE values to 1s and 0s:

{1;1;1;0;0;0;1}*{0;0;1;0;0;1;0}*{1;0;1;0;0;0;1}

And the final result looks like this:

{0;0;1;0;0;0;0}

Which goes into MATCH as the lookup array:

MATCH(1,{0;0;1;0;0;0;0})

MATCH returns 3, and the entire formula boils down to a standard INDEX MATCH formula

=INDEX(E5:E11,3)

with a final result of $17.00.

Worked Example:   Get first match cell contains in Excel

Non-array version

It is possible to add another INDEX to this formula, avoiding the need to enter as an array formula with control + shift + enter:

=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))

The INDEX function can handle arrays natively, so the second INDEX is added only to “catch” the array created with the boolean logic operation and return the same array again to MATCH. To do this, INDEX is configured with zero rows and one column. The zero row trick causes INDEX to return column 1 from the array (which is already one column anyway).

Worked Example:   Highlight approximate match lookup conditional formatting in Excel

Post navigation

Previous Post:

Weighted average in Excel

Next Post:

Win loss points calculation in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Return blank if in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • IF, AND, OR and NOT Functions Examples in Excel
  • SWITCH function example in Excel

Date Time

  • Convert Excel time to decimal hours in Excel
  • Get date from day number in Excel
  • Get day from date in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • Get days, months, and years between dates in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Categorize text with keywords in Excel
  • Group times into unequal buckets in Excel
  • Group numbers at uneven intervals in Excel
  • Calculate conditional mode with criteria in Excel

General

  • How to Create Calendar in Excel
  • How to get random value from list or table in Excel
  • How to calculate percent variance in Excel
  • How to get amount with percentage in Excel
  • How to generate random number between two numbers in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning