Returning Column Header
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:K20)
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:K20)
Will return an array of either TRUE or FALSE.
Dividing those logical values by 1:
1/(B2:K20)
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
wrote in message
...
On Feb 10, 11:53 pm, "T. Valko" wrote:
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:K20),B1:K1),"")
--
Biff
Microsoft Excel MVP
wrote in message
...
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- Hide quoted
text -
- 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:K20) part - why 2 and why 1 divided by the
reference. Thanks again - Jake
|