OFFSET??

[Jan Kronsell]

I have two columns, one with numbers, one with letters.

1 A
2 N
3 B
4 N
5 S
6 S
7 X
8 N
9 E
10 N

In anotherc column I like a list of the numbers in column Am, for which
column B contains an N.

I tried the following

IF(B1="N",OFFSET(B1,0,-1)

And this gives me

0
2
0
4
0
0
0
8
0
10


But I need the result to be like

2
4
8
10

That is only showing the numbers, that actually has a N in columbn B. I've
tried with combinations of MATCH and OFFSETR as well with no luck.

Any ideas?

Jan

 

[Bernie Deitrick]

Jan,

Use the array formula (entered using Ctrtl-Shift-Enter)

=INDEX(A:A,SMALL(IF(($B$1:$B$10="N"),ROW($B$1:$B$10),15),ROW()))

Copy down as far as you need, and put "All Done" into cell A15...

HTH,
Bernie
MS Excel MVP

 

[Jan Kronsell]

Thanks. That di it

Jan

Jan,

Use the array formula (entered using Ctrtl-Shift-Enter)

=INDEX(A:A,SMALL(IF(($B$1:$B$10="N"),ROW($B$1:$B$10),15),ROW()))

Copy down as far as you need, and put "All Done" into cell A15...

HTH,
Bernie
MS Excel MVP