Search range and return column letter if value is greater than zero

1. Search range and return column letter if value is greater than zero

Hi everyone,

I need help with the following:
I need to search a range and return the column letter if the value in the cell is greater than zero. it needs to return the first column letter (starting from column F to A in example below).

example:
A B C D E F
0 2 0 5 0 0

So if searching through the range, the result should return 'D'

this is my first contribution to the forum, your help is much appreciated,
thanks
Marwan

2. Re: Search range and return column letter if value is greater than zero

A few approaches you could use but if we assume A:F could vary then perhaps:

``Please Login or Register  to view this content.``
where A1:F1 hold original values

3. Re: Search range and return column letter if value is greater than zero

thanks this is perfect !!!

4. Re: Search range and return column letter if value is greater than zero

Given the previous ask/result, I need to sum a range. but that range is now defined by the result of the formula you provided earlier.

i always think its best explained in an example:

(i decided to attach it for mre clarity)

Please let me know if you need any further details ,
thanks again
Marwan

5. Re: Search range and return column letter if value is greater than zero

Not sure if I've followed or not but perhaps:

``Please Login or Register  to view this content.``

6. Re: Search range and return column letter if value is greater than zero

Iīm doing basically the same thing but would like to find the last column in which "0" appears (and return the column # not as a letter)

I J K L M N O P Q R S T
0 4 7 0 8 2 3 0 1 8 2 0
1 1 1 1 2 3 4 5 2 1 2 1
8 1 9 2 3 7 7 6 0 0 0 0

For instance in the last row would have Q (column #17) as a result.

Thanks

7. Re: Search range and return column letter if value is greater than zero

For your third row, the last 0 is in column T, is it not? Did you want to return T or 20?

=LOOKUP(2,1/(I2:T2=0),COLUMN(I1:T1)) should work (assuming you do want the last 0 like you said)

8. Re: Search range and return column letter if value is greater than zero

Originally Posted by ChemistB
For your third row, the last 0 is in column T, is it not? Did you want to return T or 20?

=LOOKUP(2,1/(I2:T2=0),COLUMN(I1:T1)) should work (assuming you do want the last 0 like you said)
I didnīt explain my self.
What I want to do really is to count how many "0"īs appear beginning from the LAST row
In my example would be:

1 (for the first row, since it appears in T)
0 (for the second row, since thereīs not a 0 in the last row)
4 (for the third row, since there are 4 continuos zeros since the last row -they start appearing in Q-)

9. Re: Search range and return column letter if value is greater than zero

Try this

=COLUMN(T1)-LOOKUP(2,1/(I1:T1<>0),COLUMN(I1:T1))

10. Re: Search range and return column letter if value is greater than zero

ok thanks a lot!
It worked perfect

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1