Concatenation of a variable inside a formula

Hello everyone.
I have an interesting problem and need the groups help to solve. Objective is to concatenate a variable (a text value representing columns like "AF" or "BA") inside a formula to make it a dynamic search.

This formula works fine.
=IF(SUMPRODUCT(('CT IPT Schedule'!$K$5:$K$302="Failed")*('CT IPT Schedule'!AA$5:AA$302<>""))>0,"X","")

I want to replace the column values "AA" in the formula with a text value representing the column i want to search, example "BF" or "X" (which is a column in another tab to search) to make the formula dynamic. Don't know how to replace the text "AA" with my column variable. Using "&" does not work. Ideas how to do this.

Thanks,
Kevin Smith

You could try using INDIRECT, e.g.

=IF(SUMPRODUCT(--('CT IPT Schedule'!$K$5:$K$302="Failed"),--(INDIRECT("'CT IPT Schedule'!"&A1&"$5:"&A1&"$302")<>""))>0,"X","")

Cell A1 should contain the column letters required, so if you still wanted the formula to refer to column AA then A1 should contain the text AA. If you want to refer to a column on another sheet you have to change the formula slightly and have the sheet name in A1 too.

Another way to achieve this is to use INDEX, i.e.

=IF(SUMPRODUCT(--('CT IPT Schedule'!$K$5:$K$302="Failed"),--(INDEX('CT IPT Schedule'!$5:$302,0,B1)<>""))>0,"X","")

In this version you'd put a number in B1, 1 for column A up to 256 for column IV

Used INDIRECT and it works great. Another tool in the knowledge arsenal.

Thanks very much for the advise and prompt reply.
Kevin