Lookup column headings?

R

RunsWithKnives

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,
 
M

Max

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
 
R

RunsWithKnives

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
 
B

Blueglass

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.
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top