Lookup column headings?
 

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)
Ive tried many machinations of hlookups, index, match but I cant seem to
get the thing to work,
Cheers in advance,

Lookup column headings?
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RunsWithKnives" wrote:
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)
Ive tried many machinations of hlookups, index, match but I cant seem to
get the thing to work,
Cheers in advance,

Lookup column headings?
 

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

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RunsWithKnives" wrote:
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)
Ive tried many machinations of hlookups, index, match but I cant seem to
get the thing to work,
Cheers in advance,

Lookup column headings?
 

Glad you got it working fine there
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RunsWithKnives" wrote:
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

Lookup column headings?
 

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.


"Max" wrote:

Glad you got it working fine there
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RunsWithKnives" wrote:
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

Lookup column headings?
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Blueglass" wrote:
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.