Help with SUMPRODUCT, INDEX, MATCH Array Formula

[Joyner]

I am looking for a formula that returns a value (number or text) from data that is in row one. I have a matrix from J5 to BU 85 (81 rows and 63 columns) and the data to be returns is from J1 to BU1. Only one row in the matrix will return the data from the cells in row one (only one match from column J) and if a hit (isnumber) in any column for that row, it should return the corresponding value/data from the same column in row one.

I have a formula that works to return the value I want, but I have to copy it across and down to account for all of the cells in the matrix. Then I would need to find the one row with the data, and find a way to return that to use it. The formula I have is =IF($J5=Assmt_Type,(IF(ISNUMBER(K5),INDEX(K$1:K5,1,1),"")),"")

What I would like is one formula to copy across to return the data from row one, for all rows, to find the row that has the value is column J =Assmt_Type (again only one of the rows will match).

I assume that this will be a combination of sumproduct, index, match, or a combination of other functions I haven’t considered, but I can’t figure out the combination.

Any help would be greatly appreciated.

Thanks

Using XP/2003

 

[Joyner]

Okay, maybe not as hard as I thought, I was able to do it with this formula copying across for each column.


=IF(SUMPRODUCT(--($J5:$J85=Assmt_Type),K$5:K$85)>0,K1,"")

Thanks