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

Options
✭✭✭✭

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?

## Best Answers

• ✭✭✭✭✭✭
Answer ✓
Options

Can you give an example?

Try using INDEX/COLLECT combination

• ✭✭✭✭✭✭
Answer ✓
Options

You can use an INDEX/COLLECT combo.

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

## Answers

• ✭✭✭✭✭✭
Answer ✓
Options

Can you give an example?

Try using INDEX/COLLECT combination

• ✭✭✭✭✭✭
Answer ✓
Options

You can use an INDEX/COLLECT combo.

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!