Index Match question

Jen H.

Hello, I have 2 sheets, 1 is a tracking sheet, the other is a reference sheet. I need to pull into the tracking sheet a dollar amount from the reference sheet. The dollar amount is based on the information pulled from 2 different columns - Job Title and # of hours. I tried to use a join function to simplify the index match, and created a "Concatenate" column, but am getting "Invalid Data Type" error.

Here's a screen shot of my columns:

Tracking sheet

Reference sheet

The formula needs to match the Concatenate columns in the tracking & reference sheets and bring back into the Tracking sheet the "Subtotal $ Amount" from the reference sheet.

I have tried about 100 different iterations, but the latest formula I tried looks like this:

=INDEX({Incentive Reference Sheet Range - Concatenate}, {Incentive Reference Sheet Range - $$$ amount}, MATCH([Concatenate Column]@row))

Any ideas? And, thanks so much!!!!

Kelly Moore

Hey @Jen H.

An Index/Match has the syntax of

=Index(source range you want, MATCH(what you are matching from, source range matching to, 0)). As you realized, the INDEX/MATCH can only work with one criteria which you resolved with the concatenation. You could use your concatenation if the syntax of your formula was corrected.

=INDEX({Incentive Reference Sheet Range - Concatenate}, MATCH([Concatenate Column]@row,{Incentive Reference Sheet Range - $$$ amount}, 0))

An INDEX/COLLECT allows you to collect a range based on any number of criteria. You wouldn't need any concatenation. This is a powerful formula and one you need in your arsenal of formulas.

=INDEX(COLLECT({Incentive Reference Sheet Subtotal $ Amount column}, {Incentive Reference Sheet Job Title Column}, [Job Title]@row, {Incentive Reference Sheet Shift Duration Hours column}, [Number of Hours]@row),1)

Do these give you the results you expect?

Kelly

https://help.smartsheet.com/function/index#

Jen H.

@Kelly Moore -thank you so much! This worked perfectly!!!! I will definitely use INDEX(COLLECT) in the future. This is such a powerful formula. Thanks again!!!

Kelly Moore

Hey @Jen H.

An Index/Match has the syntax of

=Index(source range you want, MATCH(what you are matching from, source range matching to, 0)). As you realized, the INDEX/MATCH can only work with one criteria which you resolved with the concatenation. You could use your concatenation if the syntax of your formula was corrected.

=INDEX({Incentive Reference Sheet Range - Concatenate}, MATCH([Concatenate Column]@row,{Incentive Reference Sheet Range - $$$ amount}, 0))

An INDEX/COLLECT allows you to collect a range based on any number of criteria. You wouldn't need any concatenation. This is a powerful formula and one you need in your arsenal of formulas.

=INDEX(COLLECT({Incentive Reference Sheet Subtotal $ Amount column}, {Incentive Reference Sheet Job Title Column}, [Job Title]@row, {Incentive Reference Sheet Shift Duration Hours column}, [Number of Hours]@row),1)

Do these give you the results you expect?

Kelly

https://help.smartsheet.com/function/index#

Jen H.

@Kelly Moore -thank you so much! This worked perfectly!!!! I will definitely use INDEX(COLLECT) in the future. This is such a powerful formula. Thanks again!!!