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

Data Analysis

  • Reverse List in Excel
  • How to calculate average last N values in a table in Excel
  • How to create dynamic reference table name in Excel
  • Managing Conditional Formatting Rules in Excel
  • How to combine 2 or more chart types in a single chart in Excel

References

  • Two-column Lookup in Excel
  • Lookup entire row in Excel
  • How to get last row in mixed data with blanks in Excel
  • How to use Excel INDIRECT Function
  • Two-way lookup with VLOOKUP in Excel

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation specific characters only
  • Excel Data validation don’t exceed total
  • Excel Data validation allow weekday only
  • Data validation must not exist in list

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

  • IF with boolean logic in Excel
  • How to use Excel OR Function
  • How to use Excel XOR Function
  • IF, AND, OR and NOT Functions Examples in Excel
  • Invoice status with nested if in Excel

Date Time

  • How to determine year is a leap year in Excel
  • Next biweekly payday from date in Excel
  • Dynamic date list in Excel
  • How to calculate working days left in month in Excel
  • Two ways to sum time over 30 minutes in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • Map text to numbers in Excel
  • Group times into 3 hour buckets in Excel
  • Running count group by n size in Excel
  • If cell contains one of many things in Excel

General

  • Index and match on multiple columns in Excel
  • Check if multiple cells have same value in Excel
  • How to fill cell ranges with random text values in Excel
  • How to calculate percent of students absent in Excel
  • Basic numeric sort formula in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning