Index Match Row Heading?

[Javier Diaz]

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

 

[bj]

are you looking for a sum >0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)>0,"O","")&if sum(P286500)>0,"P","") &...
or
=if(sum(O23:O6500)>0,O2,"")&if sum(P286500)>0,P2,"") &...
or
=if(countif(O23:O6500,">0")>0,"O","")&if(countif(P236500,">0")>0,"P") & ...
or
....

depending on what you want, different formulas would be appropriate

 

[Javier Diaz]

OK BJ, let me look into these. Thanks I'll get back to you shortly.

are you looking for a sum >0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)>0,"O","")&if sum(P286500)>0,"P","") &...
or
=if(sum(O23:O6500)>0,O2,"")&if sum(P286500)>0,P2,"") &...
or
=if(countif(O23:O6500,">0")>0,"O","")&if(countif(P236500,">0")>0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

 

[Javier Diaz]

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

are you looking for a sum >0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)>0,"O","")&if sum(P286500)>0,"P","") &...
or
=if(sum(O23:O6500)>0,O2,"")&if sum(P286500)>0,P2,"") &...
or
=if(countif(O23:O6500,">0")>0,"O","")&if(countif(P236500,">0")>0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

 

[bj]

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O6500>0)*column(O23)+(P236500>0)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

are you looking for a sum >0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)>0,"O","")&if sum(P286500)>0,"P","") &...
or
=if(sum(O23:O6500)>0,O2,"")&if sum(P286500)>0,P2,"") &...
or
=if(countif(O23:O6500,">0")>0,"O","")&if(countif(P236500,">0")>0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

 

[Toppers]

This returns the column header if only one column has data > 0

=INDEX(A11,SUMPRODUCT(--(A2:E9>0)*COLUMN(A:E)))

Does this help?

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O6500>0)*column(O23)+(P236500>0)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

are you looking for a sum >0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)>0,"O","")&if sum(P286500)>0,"P","") &...
or
=if(sum(O23:O6500)>0,O2,"")&if sum(P286500)>0,P2,"") &...
or
=if(countif(O23:O6500,">0")>0,"O","")&if(countif(P236500,">0")>0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

 

[Toppers]

.... dooh ... it doesn't for multiple vlaues... back to the drawing board.

This returns the column header if only one column has data > 0

=INDEX(A11,SUMPRODUCT(--(A2:E9>0)*COLUMN(A:E)))

Does this help?

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O6500>0)*column(O23)+(P236500>0)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

:

are you looking for a sum >0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)>0,"O","")&if sum(P286500)>0,"P","") &...
or
=if(sum(O23:O6500)>0,O2,"")&if sum(P286500)>0,P2,"") &...
or
=if(countif(O23:O6500,">0")>0,"O","")&if(countif(P236500,">0")>0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

 

[Toppers]

=INDEX(A11,SUMPRODUCT(--(A2:E9>0)*COLUMN(A:E))/SUMPRODUCT(--ISNUMBER((A2:E9))))

This returns the column header if only one column has data > 0

=INDEX(A11,SUMPRODUCT(--(A2:E9>0)*COLUMN(A:E)))

Does this help?

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O6500>0)*column(O23)+(P236500>0)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

:

are you looking for a sum >0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)>0,"O","")&if sum(P286500)>0,"P","") &...
or
=if(sum(O23:O6500)>0,O2,"")&if sum(P286500)>0,P2,"") &...
or
=if(countif(O23:O6500,">0")>0,"O","")&if(countif(P236500,">0")>0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

 

[Toppers]

=INDEX(A11,SUMPRODUCT(--(A2:E9>0)*COLUMN(A:E))/COUNTIF(A2:E9,">0"))

This returns the column header if only one column has data > 0

=INDEX(A11,SUMPRODUCT(--(A2:E9>0)*COLUMN(A:E)))

Does this help?

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O6500>0)*column(O23)+(P236500>0)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

:

are you looking for a sum >0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)>0,"O","")&if sum(P286500)>0,"P","") &...
or
=if(sum(O23:O6500)>0,O2,"")&if sum(P286500)>0,P2,"") &...
or
=if(countif(O23:O6500,">0")>0,"O","")&if(countif(P236500,">0")>0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

 

[Harlan Grove]

=INDEX(A11,SUMPRODUCT(--(A2:E9>0)*COLUMN(A:E))/COUNTIF(A2:E9,">0"))

....

A lot of effort to avoid the array formula

=INDEX(A1:E1,MIN(IF(A2:E9>0,COLUMN(A1:E1))))

 

[Javier Diaz]

Ok, let me see how this one works and I'll get back you Toppers. Thanks.
Sorry I didnt get back sooner, but I didnt get alerted via email that I got a
response. Sometimes that works and sometimes it doesnt.

=INDEX(A11,SUMPRODUCT(--(A2:E9>0)*COLUMN(A:E))/COUNTIF(A2:E9,">0"))

This returns the column header if only one column has data > 0

=INDEX(A11,SUMPRODUCT(--(A2:E9>0)*COLUMN(A:E)))

Does this help?

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O6500>0)*column(O23)+(P236500>0)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

:

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

:

are you looking for a sum >0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)>0,"O","")&if sum(P286500)>0,"P","") &...
or
=if(sum(O23:O6500)>0,O2,"")&if sum(P286500)>0,P2,"") &...
or
=if(countif(O23:O6500,">0")>0,"O","")&if(countif(P236500,">0")>0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

 

[ryguy7272]

Won't a pivot table do it for you?

 

[Javier Diaz]

It probably would, but I think with a simple Index Match formula I could get
the result I'm looking for. The spreadsheet is already a monster and it has
pivots and validations list that I'm working to get rid off via automated
data population with these formulas.

Won't a pivot table do it for you?

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater >0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?