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

Data Analysis

  • Get column index in Excel Table
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Excel Frequency Function Example
  • How to sum a total in multiple Excel tables
  • How to conditionally sum numeric data in an Excel table using SUMIFS

References

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Excel Advanced Lookup using Index and Match Functions
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to get address of named range in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation require unique number
  • Excel Data validation with conditional list
  • Data validation must not exist in list
  • Excel Data validation whole percentage only

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.

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.

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).

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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

  • Invoice status with nested if in Excel
  • SWITCH function example in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FALSE Function
  • How to use Excel TRUE Function

Date Time

  • How to get same date next year or previous year in Excel
  • How to determine year is a leap year in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • Get project midpoint in Excel
  • Convert Excel time to decimal seconds

Grouping

  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel
  • Group times into 3 hour buckets in Excel
  • Map inputs to arbitrary values in Excel
  • Categorize text with keywords in Excel

General

  • Basic numeric sort formula in Excel
  • Currency vs Accounting Format in Excel
  • How to increase by percentage in Excel
  • How to generate random date between two dates in Excel
  • Convert column number to letter in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning