Can the search value in a MATCH function be based on data in the current row?

Rachel Harned

I am working on creating a calendar of training programs for my company and how many trainers each one needs. I have a separate sheet that already contains the total number of trainers required for each program and would like to use a cross sheet formula to return that value on the program calendar. I have used Index and Match (formula below) to find and return the value that I want but I have to manually input the search value in quotes for each program and would like to make it easier if possible.

=INDEX({TDP Program Schedule Template Total TDP}, MATCH("Basecamp PTB Onboarding", {TDP Program Schedule Template Assignment}, 0))

Is there a way to Index and Match data based on data in the current sheet/row rather than a specific search value? Ideally, I would like a formula that could search for the value that I put in the Program column in the "Program Calendar" sheet in the Program column of my "Program Schedule Template" sheet and return the corresponding number from that row for Total TDP.

Paul Newcome

Most certainly...

=INDEX({TDP Program Schedule Template Total TDP}, MATCH(Program@row, {TDP Program Schedule Template Assignment}, 0))

You would replace the specific text with a direct cell reference.

Paul Newcome

Most certainly...

=INDEX({TDP Program Schedule Template Total TDP}, MATCH(Program@row, {TDP Program Schedule Template Assignment}, 0))

You would replace the specific text with a direct cell reference.

Rachel Harned

Thank you @Paul Newcome! I probably should have figured that out :)

Paul Newcome

Happy to help. 👍️