INDEX/MATCH with more than two criteria

Mike Tomei

Can I use an INDEX/MATCH formula with the attached example sheet? From a separate sheet I would like to use an INDEX/MATCH formula that looks to match all three criteria and returns the correct dollar amount. Is this possible, or is there a better formula that can be used? I've gotten my brain pretty confused on this one. Thanks.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326170#Comment_326170

That is possible, but might be kind of messy to implement due to your data structure. Things tend to work better when your data is formulated in rows. In other words, each criteria is in its own column, and is used to determine which value you collect from the same row. Now the below is not necessarily a solution for you, since I don't really know what your criteria are or how they relate to each other, but this might point you in a direction that will help:

Say these are your columns and data:

In CriteriaCheck1, you'd put:

=COUNTIFS([Criteria 1]@row, "X", [Criteria 2]@row, "Y", [Criteria 3]@row, "Z")

In CriteriaCheck2, you'd put:

=COUNTIFS([Criteria 1]@row, "X", [Criteria 4]@row, "C", [Criteria 5]@row, "D")

So then you'd end up with a "1" in the CriteriaCheck cells where the conditions are met:

On your remote sheet:

=IF(ISNUMBER(INDEX({Sheet 1 Value 1}, MATCH(1, {Sheet 1 CriteriaCheck1}, 0))), INDEX({Sheet 1 Value 1}, MATCH(1, {Sheet 1 CriteriaCheck1}, 0)), INDEX({Sheet 1 Value 2}, MATCH(1, {Sheet 1 CriteriaCheck2}, 0)))

Jeff Reisman

@Mike Tomei

You could use a helper column with COUNTIFS to determine if the three criteria are met and set a value, then use INDEX/MATCH to collect your dollar amount by matching the result of the COUNTIFS.

It's hard to tell from your screenshot exactly what criteria you are looking at. Your question says three criteria but the screenshot shows five?

Mike Tomei

Hi Jeff. Thanks for the message. I was considering an INDEX/MATCH formula that, for example, if it met Criteria 1, Criteria 2, and Criteria 3, it would return the value of $10.00. If it met Criteria 1, Criteria 4, and Criteria 5, it will return the value of $20.00. Is that possible?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326170#Comment_326170

That is possible, but might be kind of messy to implement due to your data structure. Things tend to work better when your data is formulated in rows. In other words, each criteria is in its own column, and is used to determine which value you collect from the same row. Now the below is not necessarily a solution for you, since I don't really know what your criteria are or how they relate to each other, but this might point you in a direction that will help:

Say these are your columns and data:

In CriteriaCheck1, you'd put:

=COUNTIFS([Criteria 1]@row, "X", [Criteria 2]@row, "Y", [Criteria 3]@row, "Z")

In CriteriaCheck2, you'd put:

=COUNTIFS([Criteria 1]@row, "X", [Criteria 4]@row, "C", [Criteria 5]@row, "D")

So then you'd end up with a "1" in the CriteriaCheck cells where the conditions are met:

On your remote sheet:

=IF(ISNUMBER(INDEX({Sheet 1 Value 1}, MATCH(1, {Sheet 1 CriteriaCheck1}, 0))), INDEX({Sheet 1 Value 1}, MATCH(1, {Sheet 1 CriteriaCheck1}, 0)), INDEX({Sheet 1 Value 2}, MATCH(1, {Sheet 1 CriteriaCheck2}, 0)))