Lookup column headings?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Folks,
I have a Spread sheet that is 1430 rows deep by 39 columns long. I have
extracted from this the min value of each row into another spread sheet. Now
I want to use these min values to look back at the original spread sheet and
find what column the min value came from (i.e. its heading)
I’ve tried many machinations of hlookups, index, match but I can’t seem to
get the thing to work,
Cheers in advance,
 
One way ..

Assume source data in Sheet1, within cols B to AN (39 cols),
with col headers in B1:AN1, data from row2 down

In Sheet2,

Placed in say, B2:
=INDEX(Sheet1!$B$1:$AN$1,MATCH(MIN(Sheet1!B2:AN2),Sheet1!B2:AN2,0))
would return the col header corresponding to the min value for row2 in Sheet1

Copy B2 down by 1430 rows to return for all data rows in Sheet1

Note that in the event of any ties in the min value within each row, only
the leftmost col header will be returned
 
Cheers Max,
Not exactly what I was after as I already had the minimum values but gave me
enough to work with. I ended up using
=INDEX(Sheet1!$B$1:$AN$1,MATCH(Sheet2!B2,Sheet1!B2:AN2,0)) where Sheet2!B2
was my list of values.

Thanks again,

RunsWithKnives
 
Hello Max,
Here's a related question, how do I test a pivot table for the presence of
non-zero values and return all applicable column headings.
Thus:

ID A B C D E F G
John 0 0 0 0 0 0 0
Paul 1 1 1 1 1 1 1
Ringo 0 0 1 0 0 0 0
George 0 0 0 0 1 0 0

John would return no values.
Paul would return A,B,C,D,E,F,G
Ringo would return C
George would return E

Thank you.
 
Assume source data as posted is within cols A to H in a sheet named: X

In a helper sheet: R,
Put in B2:
=IF(OR(X!B2="",X!B2=0),"",X!B2+COLUMNS($A:A)-1)
Copy B2 across to H2, fill down as far as required to cover the extent of
source data in X

Then in a new sheet,
Assuming the ids are listed in A2 down
(these need not be in the same order as that in X)

Put in B2
=IF(COLUMNS($A:A)>COUNT(OFFSET('R'!$B$1:$H$1,MATCH($A2,X!$A:$A,0)-1,)),"",INDEX(X!$B$1:$H$1,SMALL(OFFSET('R'!$B$1:$H$1,MATCH($A2,X!$A:$A,0)-1,),COLUMNS($A:A))))
Copy B2 across to H2, fill down as far as required to cover the extent of
ids listed in col A
 
Nice.
Whether is this possible
Find the zero values headings as comma seperated values

In the same case How to get
John would return A,B,C,D,E,F,G
Paul would return no values.
Ringo would return A,B,D,E,F,G
George would return A,B,C,D,F,G

J@i
 
Back
Top