How to use multiple Matches in an Index + Match formula.

Cruz Ortiz

Hi community,

Trying to use multiple conditions (matches) to pull data from a table (range).

Currently, the Index + Match formula combination is only able to use ONE match condition.

Can you use 2 or more matches with Index formula?

Leibel S

@Cruz Ortiz

Can you give an example?

Try using INDEX/COLLECT combination

Paul Newcome

You can use an INDEX/COLLECT combo.

=INDEX(COLLECT({Range To Pull}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria), 1)

Leibel S

@Cruz Ortiz

Can you give an example?

Try using INDEX/COLLECT combination

Paul Newcome

You can use an INDEX/COLLECT combo.

=INDEX(COLLECT({Range To Pull}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria), 1)

Cruz Ortiz

I thank @Leibel S & @Paul Newcome

The solution almost work, but unable to pull data from cross reference sheets. See fields between {}

All fields are Contact list type with true data.

=IFERROR(INDEX(COLLECT({Person 3}, {CAUSER}, [Department Defect Occurred]@row, {WC}, [Work Center Occurred]@row), 1)

Result: #INVALID VALUE

Paul Newcome

That is basically saying there are no rows that meet your range/criteria sets.

What do you get when you use

=COUNTIFS({CAUSER}, [Department Defect Occurred]@row, {WC}, [Work Center Occurred]@row)

Cruz Ortiz

 @Leibel S & @Paul Newcome

Finally it worked. Page needed some time to refresh 100's of cross referenced links and it showed that error.

Once I ran the COINFIFS too, result was = 1 which is exactly the correct result.

Your support is very much appreciated.

Thanks