Returning Column Header

[jakeo1a1]

Hi all - I have the following columns and data
My Column headers are Item Code - Item Description and then Week
numbers for the year
The amount of sales for each item are scattered under the week number
- there are some empty cells and some cells contain 0

 

[jakeo1a1]

Hi all - I have the following columns and data
My Column headers are Item Code - Item Description and then Week
numbers for the year
The amount of sales for each item are scattered under the week number
- there are some empty cells and some cells contain 0

Sorry - didn't finish - so what I want to do is enter a column before
the weeks start and in the column pull the column header (week number)
for the last cell in the row that is greater than 0 - so if for item
"A" the last sales (number greater than 0) is in column G I want to
return the value in row 1 of that column - which would be the week
number Thanks - Jake

 

[T. Valko]

Try this:

Assume column headers are in the range B1:K1
Sales figures in the range B2:K2. There is only numbers or empty cells in
this range.

=IF(COUNTIF(B2:K2,">0"),LOOKUP(2,1/(B2:K2>0),B1:K1),"")

 

[jakeo1a1]

Try this:

Assume column headers are in the range B1:K1
Sales figures in the range B2:K2. There is only numbers or empty cells in
this range.

=IF(COUNTIF(B2:K2,">0"),LOOKUP(2,1/(B2:K2>0),B1:K1),"")

--
Biff
Microsoft Excel MVP






- Show quoted text -

Biff - thanks so much - works perfectly. I'm trying to figure out how
it works - I read up on the Lookup function - the part I'm trying to
get is the 2,1/(B2:K2>0) part - why 2 and why 1 divided by the
reference. Thanks again - Jake

 

[T. Valko]

Here's how it works:

LOOKUP takes these arguments:

LOOKUP(lookup_value,lookup_vector,result_vector)

In our formula the lookup_value is 2

The lookup_vector is 1/(B2:K2>0)

The result_vector is B1:K1

This means we want to find the value 2 in the lookup_vector and return the
corresponding value from the result_vector.

This portion of the lookup_vector:

(B2:K2>0)

Will return an array of either TRUE or FALSE.

Dividing those logical values by 1:

1/(B2:K2>0)

Will result in an array of either 1 or a #DIV/0! error

1/(TRUE) = 1
1/(FALSE) = #DIV/0!

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

That's where the lookup_value of 2 comes into play. Since our lookup_vector
comprises an array of 1s and #DIV errors the *LAST* value that is less than
the lookup_value has to be a 1. So, the formula returns the header that
corresponds to the *last* 1 in the lookup_vector.

--
Biff
Microsoft Excel MVP

Try this:

Assume column headers are in the range B1:K1
Sales figures in the range B2:K2. There is only numbers or empty cells in
this range.

=IF(COUNTIF(B2:K2,">0"),LOOKUP(2,1/(B2:K2>0),B1:K1),"")

--
Biff
Microsoft Excel MVP






- Show quoted text -

Biff - thanks so much - works perfectly. I'm trying to figure out how
it works - I read up on the Lookup function - the part I'm trying to
get is the 2,1/(B2:K2>0) part - why 2 and why 1 divided by the
reference. Thanks again - Jake