Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows that contain specific values in Excel
  • How to reference named range different sheet in Excel
  • Approximate match with multiple criteria in Excel
  • How to retrieve first match between two ranges in Excel
  • How to get relative column numbers in a range in Excel

Data Analysis Examples

  • How to Create Column Chart in Excel
  • How to Use Solver Tool in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • Conditional Formatting Color Scales Examples in Excel

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation with conditional list
  • How To Create Drop-down List in Excel
  • Excel Data validation require unique number
  • Prevent invalid data entering in specific cells

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:   Two-way lookup with VLOOKUP 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:   Find missing values 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:   Find longest string in column in Excel

Post navigation

Previous Post:

Index and match on multiple columns in Excel

Next Post:

Lookup entire row 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
  • How to create email address from name in Excel
  • How to split text with delimiter in Excel
  • LEN, LENB functions: Description, Usage, Syntax, Examples and Explanation
  • How to count total words in a cell in Excel
  • Split dimensions into two parts in Excel Worksheet
  • Add decimal hours to time in Excel
  • Steps to create Dynamic calendar grid in Excel
  • Get month name from date in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
  • COUPNUM function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest for given period in Excel
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • How to Calculate Tax Rates in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles