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

Data Analysis

  • Reverse List in Excel
  • Excel Line Chart
  • Remove Duplicates Example in Excel
  • How To Filter Data in Excel
  • How to Use Solver Tool in Excel

References

  • How to use Excel FORMULATEXT function
  • How to use Excel MATCH Function
  • Extract data with helper column in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Last row number in range

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation whole percentage only
  • Excel Data validation allow uppercase only
  • Data validation must not exist in list
  • Excel Data validation don’t exceed total

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

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel OR Function
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel
  • IF with boolean logic in Excel

Date Time

  • Get days between dates ignoring years in Excel
  • Get days, hours, and minutes between dates in Excel
  • How to show last updated date stamp in Excel
  • Sum through n months in Excel
  • Count birthdays by month in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Map inputs to arbitrary values in Excel
  • Running count group by n size in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign data to groups in Excel

General

  • How to make excel worksheets print on one page?
  • How to calculate percentage discount in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • Currency vs Accounting Format in Excel
  • 44 Practical Excel IF function Examples
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning