Lookup value greater than 0 in column C, and return column A

[cpis0002]

Hi, Hopefully the name is pretty self explanatory...

I have Column C which will be basically empty apart from a 2 entries... 1 negative, and 1 positive number.

I want a formula to find the postive (greater than 0), and return the name of the data in column A... and same for the negative.

Any ideas?

 

[Richard Schollar]

Hi

Perhaps:

=LOOKUP(2,1/(C1:C1000>0),A1:A1000)

for the value > 0 and

=LOOKUP(2,1/(C1:C1000<0),A1:A1000)

for < 0.

 

[Lewiy]

Try this:

=INDEX(A:A,MATCH(MAX(C:C),C:C,FALSE))

 

[Krishnakumar]

Hi,

=INDEX(A1:A1000,MATCH(TRUE,C1:C1000>0,0))

Confirmed with Ctrl+Shift+Enter

Richard's will return last value where as mine return first.

HTH

 

[cpis0002]

Hi, Thanks to both of you.

Richard... can you tell me why the middle part of the Lookup function works, lol... if I put an = in front of the criteria and leave it by itself it gives an error. Thanks for the working formula, but would like to understand it.

Lewiy... I have seen the Index and Match used quite alot on MrExcel. I am quite good at excel, but never used these 2 functions. Could you please break it down so I know how and why they work.

Thanks

 

[Richard Schollar]

=LOOKUP(2,1/(C1:C1000>0),A1:A1000)

C1:C1000>0 returns an array of True/Falses dependent on whether the relevant cell's value is greater than 0.

Each element of this array is then used as the denominator in the division of 1 and returns 1 for 1/True and an error (ie #DIV/0!) for 1/False.

The clever bit is that Lookup ignores the error values, so the only results returned will be where you have no errors ie the 1/True values.

Since you only have one value > 0 in your range, then that will be the lookup value returned (used to index the formula return value in A1:A1000).

Make sense, or just caused more confusion?

 

[Aladin Akyurek]

The formula with MAX() is faster...

 

[Lewiy]

INDEX requires 2 or 3 arguments, an array, a row number and a column number. It will return the value of the cell that this refers to, so if you had:
=INDEX(B1:C10,3,2)
It will return the value in cell C3 (the third row and second column of the specified array). If you use a single dimensional array, i.e. A1:A10, then you needn’t use the column number in the formula.

MATCH basically looks up the specified value in a list and returns the row number of the list where it finds it (a bit like VLOOKUP but you get a location rather than a value). You can then use this within the INDEX formula to specify the row you want.

The result in this instance is basically a VLOOKUP which can look to the left as well as the right.

Hope that all makes sense!

 

[cpis0002]

Richard... that made sense to me... never knew about that feature. Will definately use it again

Lewiy... I understood that too, still abit tricky for me but at least now I can read the formula when I see it, hehe



Now, another problem... I have the same thing on another sheet, but I need it to give me a list of all the positive values, and a lost of all the negative values... So it will find any value which is not equal to zero and give me the text of each one found in column A as before.

I would do it by creating a 2 columns next to the data which numbers the postive data in one column, and negative data in the next.
That is: cell D150 will have the formula:
=IF(C150>0,COUNT($D$5:$D149)+1,""))

and then a lookup formula to lookup values (1,2,3,etc) and return the data in column A... but this is alot of work since I have a large amount of columns...

If you can come up with a better idea, please let me know.
Thanks again