Excel 2007 : Using Sumproduct with row to identify the row of a match?

Hi,

Here is my scenario:

I have two workbooks one called "Consumer Groups" (this is where all the data is) and the other "Consumer Support Staff".

I would like to use the boolean aspect of Subproduct to match a multiple condition and then maybe Index and match or possibly offset and column to copy the column title where this match is taking place?

So far this works on the "Consumer Support Staff" worksheet, I get a T returned:

=IF(SUMPRODUCT(-('Consumer Groups'!$A$3:$A$89='Consumer
Support Staff'!J3)*('Consumer Groups'!$B$3:$CG$89
="X")),"T","F")

I would like to identify the column in the 'Consumer Groups'!$B$3:$CG$89 range where this argument is true and then copy the column title which is in row 2 to the "Consumer Support Staff" worksheet.

Does anyone have any insights or solutions that they would care to share?

Thanks.

Thank-you Haseeb A ... that worked like a dream ... I will have to study it to get a full understanding of all of its constituent parts.

Oops ... I was a little hasty ...the solution works if there is only one Support Staff providing Service X, but if there are more than one Support Staff then I get a 0.

As an explanation Service X is a license to provide that particular service .... so there will be sometimes 5 Support Staff who all have that particular License with that particular Consumer.

My apologies for the oversight, I should have made that clear in the original posting.

Is there a way to still use this function to get the list of Support Staff with license X for this particular Consumer?

Thanks again.

Thanks Haseeb A .... I have decided to use vba to solve this problem ... thanks again for your stella work.