Linking a cell to the column in which it is found.

Posted by Mike Beerman on November 20, 2001 2:30 PM

I have an array of data with each column titled. I would like to search the data using the LARGE function and assign that number to a cell. I would also like to identify the column that the number was found in. For example if the data array contained the number 300 in the column "Vega" I would like to paste the 300 in one cell using the LARGE function and then automatically paste the column title "Vega" in the cell adjacent. I would like to do this in such a way as to update both fields automatically if another larger number is entered in a different column.

Posted by Aladin Akyurek on November 20, 2001 3:33 PM

I believe you have data like this:

{"Vega","Axza";
200,100;
100,12;
20,24;
40,56;
100,80;
140,90}

Suppose A1:B7 houses the above data. The column headings Vega, etc are in row 1.

In D1 enter: =LARGE(A2:B7,1) [ to compute the largest number ]

This is of course equivalent to =MAX(A2:B7)

In E1 enter: =IF(D1,IF(COUNTIF(A2:B7,D1)=1,INDIRECT(ADDRESS(1,SUMPRODUCT((A2:B7=D1)*(COLUMN(A2:B7))))),"The largest occurs more than once."),"")

This formula will give you the column heading of the column where the single occurrence of the largest number is. we can't just assume that the largest number wouldn't occur more than once in the same or multiple columns.

Regards,

Aladin

========