output row number for corresponding non-zero value of match functi
 

Hello,

I have a list of items as shown below:

A .5
B
B .25
B .25
C
C 1

If a given letter has a value assigned to it, all of the instances of that
letter will have the same value. For example, all of the B's that have
values assigned all share the same value of .25 But the first B doesn't have
a value. (The reason for this is that there is another variable column that
adds another dimension, but I just simplied the problem here.)

I would like to be able to return that shared value, but I am having
difficulty doing so. I tried the Match command to pick out the row number to
incorporate it into other functions, but it only picks out the first
instance. This won't work because in some cases, the first instance is a
blank which returns a zero. I want to return the non-zero value.

Any suggestions?

output row number for corresponding non-zero value of match functi
 

Try this array formula** :

=INDEX(B1:B10,MATCH(1,(A1:A10="B")*(ISNUMBER(B1:B1 0)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"lkd" wrote in message
...
Hello,

I have a list of items as shown below:

A .5
B
B .25
B .25
C
C 1

If a given letter has a value assigned to it, all of the instances of that
letter will have the same value. For example, all of the B's that have
values assigned all share the same value of .25 But the first B doesn't
have
a value. (The reason for this is that there is another variable column
that
adds another dimension, but I just simplied the problem here.)

I would like to be able to return that shared value, but I am having
difficulty doing so. I tried the Match command to pick out the row number
to
incorporate it into other functions, but it only picks out the first
instance. This won't work because in some cases, the first instance is a
blank which returns a zero. I want to return the non-zero value.

Any suggestions?

output row number for corresponding non-zero value of match functi
 

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

Assuming your data is in ColA and ColB. To return row number

=MIN(IF((A1:A100="b")*(B1:B100<""),ROW(A1:A100)))

'First value for "b" from col B
=INDEX(B1:B100,MIN(IF((A1:A100="b")*(B1:B100<""), ROW(A1:A100))))


If this post helps click Yes
---------------
Jacob Skaria


"lkd" wrote:

Hello,

I have a list of items as shown below:

A .5
B
B .25
B .25
C
C 1

If a given letter has a value assigned to it, all of the instances of that
letter will have the same value. For example, all of the B's that have
values assigned all share the same value of .25 But the first B doesn't have
a value. (The reason for this is that there is another variable column that
adds another dimension, but I just simplied the problem here.)

I would like to be able to return that shared value, but I am having
difficulty doing so. I tried the Match command to pick out the row number to
incorporate it into other functions, but it only picks out the first
instance. This won't work because in some cases, the first instance is a
blank which returns a zero. I want to return the non-zero value.

Any suggestions?