Lets say I have the following table:

 A B C 1 1 1 0 2 0 1 1 3 3 1 6 4 0 2 2 5 5 2 3

I would like to create 2 new columns with the help of a formula. one column for all the values=1 in B column and
one column for all the values=2 in B column. Those columns will return the matched values of column C by the order of column A but
when A=0 formula would do nothing and the next calculation would be written in that same cell.
In this example it should look like that:
 New1 New2 0 3 6

I tried to use something like that (for new1):
Code:
`=IF(AND(B1=1,A1<>0),VLOOKUP(B1,\$B1:\$C\$5,2,FALSE))`
I dragged the formula and then I tried filtering the "FALSE" but it is affecting the original matrix.

Best

2. Right off it looks like you are trying to use B1:C5 as a lookup table - but you can't have the same value in B with different values in C.

3. Actually It works as a lookup table, the problem is that where A=0 I get "False" that I'm not able to get rid of later.

4. Try this in D1:
=IF(A1<>0,IF(B1=1,C1,0),0)
and try this in E1:
=IF(A1<>0,IF(B1=2,C1,0),0)

5. Ok so that does work but it still holds the same problem:
1. value_if_false is "0" so I replaced it because 0 is also a score in C column and we won't able to get rid of the A=0 results that way (so that is fixed if I change what you wrote).

2. you can't delete/filter the "false" results and that is my main problem. I want continuous arrays as I wrote in the example.

Thanks

6. I guess I'm not clear what outcome you want. So your goal is to "filter" the FALSE, meaning the instances where A=0 and B=1 or B=2?

7. Yes. More accurate way to put it is that I want just to pull out the C values where A is not equal "0", one column will be the C values where B=1 and the other column the C values where B=2 (exactly as the table I wrote in the example: new1 and new2).

If there is a way to do that without the step of filtering that would be great.

I'll just say that there's the possibility to filter the original matrix first and then using the formulas but it still creates problems later on.

8. So if you put a data filter on Col A, filter so you will see all EXCEPT the zeros. Does that work for you or do you need help to learn how to put a data filter on Col A?

9. As you can see I've edited my reply because I knew you're getting there
I've tried it but it seems to still creating a problem when dragging the formula after filtering.

10. I think what I would do is put in your two formulas in the 2 new columns, then filter on A to get rid of the zeros - or did you mean that you don't want a DATA FILTER?
OR
you could just set up a pivot table.
Again, what is your goal with the figures that you get? How is the info used? Why are you doing this? I still feel like if I had the whole picture, I could propose another/alternate/better solution : )

Page 1 of 2 1 2 Last