# Advanced LOOKUP: Return list of all positive/negative values

#### cpis0002

##### Board Regular
I have a large table, in this problem, I am using column B and column J rows 9 to 554.

I need a formula to give me a list of all the positive values, and a list 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 B.

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 K150 will have the formula:
=IF(J150>0,COUNT(\$K\$5:\$K149)+1,""))

and then a lookup formula to lookup values (1,2,3,etc) and return the data in column B... but this is alot of work since I have a large amount of columns (since I have other columns like column J which need the same process somewer else)...

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### xenou

##### MrExcel MVP
All positive values in \$B\$9:\$J\$554 (assumption: cells have numeric values)
=CountIf(\$B\$9:\$J\$554,">=0")

All negative values in \$B\$9:\$J\$554 (assumption: cells have numeric values)
=CountIf(\$B\$9:\$J\$554,"<0")

To return data in column B in Column K
In cell K1, formula is
=B1
Then fill down.

I don't understand why you have given cell K150 as an example, or why you are counting only in cells \$K\$5:\$K149, and adding one. Please try to explain again if you still need help (try to use precise cell references for everything -- not just "count values" but count values in these cells..., not just "lookup values" but lookup values in these cells..., and so on).

Regards

Replies
3
Views
63
Replies
1
Views
90
Replies
3
Views
549
Replies
2
Views
49
Replies
3
Views
909

1,191,118
Messages
5,984,749
Members
439,907
Latest member
Kayfabe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back