software

Hi guys; Ive been using an article on 'how to look up a value in alist and
return multiple corresponding values' from an Excel MVP but using his data
example and his formula doesn't work. I am trying to match values from a
column with a set cell value and return the row numbers one after the other.
I.e if the value 'boots' is in Cells B10, B15 and B20, I want in Cells C1, C2
and C3 the values 10, 15 and 20. He suggests using the following function(s).

=SMALL(IF($B$1:$B$20=$A$1,ROW($B$1:$B$20)),ROW(1:1 ))

But is does not work, I have found the error is in the if function and a) it
not returning an array for the 'small' function and b)matching multiple
values in B column to $A$1.

Thanks

Works for me if I array enter the formula and then copy down.

quot;Chillerquot; wrote:

gt; Hi guys; Ive been using an article on 'how to look up a value in alist and
gt; return multiple corresponding values' from an Excel MVP but using his data
gt; example and his formula doesn't work. I am trying to match values from a
gt; column with a set cell value and return the row numbers one after the other.
gt; I.e if the value 'boots' is in Cells B10, B15 and B20, I want in Cells C1, C2
gt; and C3 the values 10, 15 and 20. He suggests using the following function(s).
gt;
gt; =SMALL(IF($B$1:$B$20=$A$1,ROW($B$1:$B$20)),ROW(1:1 ))
gt;
gt; But is does not work, I have found the error is in the if function and a) it
gt; not returning an array for the 'small' function and b)matching multiple
gt; values in B column to $A$1.
gt;
gt; Thanks

Chiller wrote...
gt;Hi guys; Ive been using an article on 'how to look up a value in alist and
gt;return multiple corresponding values' from an Excel MVP but using his data
gt;example and his formula doesn't work. I am trying to match values from a
gt;column with a set cell value and return the row numbers one after the other.
gt;I.e if the value 'boots' is in Cells B10, B15 and B20, I want in Cells C1, C2
gt;and C3 the values 10, 15 and 20. He suggests using the following function(s).
gt;
gt;=SMALL(IF($B$1:$B$20=$A$1,ROW($B$1:$B$20)),ROW(1: 1))
gt;
gt;But is does not work, I have found the error is in the if function and a) it
gt;not returning an array for the 'small' function and b)matching multiple
gt;values in B column to $A$1.

The formula works for me as long as I enter it as an array formula. Are
you entering it as an array formula? Actually, what do you mean by
quot;doesn't workquot;? Returns wrong numeric results? Returns error values?
Excel doesn't accept entry of the formula? Excel crashes when you enter
the formula, smoke boils out of your monitor, and meteorites change
course to pulverize your PC?OK. It would make sense that that bloke was right and I was wrong. Have just
checked how to enter an array formula, still don't get it so any help there
would be appreciated. Tried putting {} around the formula, as well as typing
it out then pressing CTRL ALT ENTER but maybe doing something wrong. Still
not working

Ok

quot;Harlan Grovequot; wrote:

gt; Chiller wrote...
gt; gt;Hi guys; Ive been using an article on 'how to look up a value in alist and
gt; gt;return multiple corresponding values' from an Excel MVP but using his data
gt; gt;example and his formula doesn't work. I am trying to match values from a
gt; gt;column with a set cell value and return the row numbers one after the other.
gt; gt;I.e if the value 'boots' is in Cells B10, B15 and B20, I want in Cells C1, C2
gt; gt;and C3 the values 10, 15 and 20. He suggests using the following function(s).
gt; gt;
gt; gt;=SMALL(IF($B$1:$B$20=$A$1,ROW($B$1:$B$20)),ROW(1: 1))
gt; gt;
gt; gt;But is does not work, I have found the error is in the if function and a) it
gt; gt;not returning an array for the 'small' function and b)matching multiple
gt; gt;values in B column to $A$1.
gt;
gt; The formula works for me as long as I enter it as an array formula. Are
gt; you entering it as an array formula? Actually, what do you mean by
gt; quot;doesn't workquot;? Returns wrong numeric results? Returns error values?
gt; Excel doesn't accept entry of the formula? Excel crashes when you enter
gt; the formula, smoke boils out of your monitor, and meteorites change
gt; course to pulverize your PC?
gt;
gt;

Ok, now it makes sense. You do not put the {} around the formula. When you
enter it correctly, Excel does that for you. You were very close with Ctrl
Alt Enter, but it is Cntl Shift Enter.

HTH

BTW, I chuckled when I read Harlan's response. (meteorites

quot;Chillerquot; wrote:

gt; OK. It would make sense that that bloke was right and I was wrong. Have just
gt; checked how to enter an array formula, still don't get it so any help there
gt; would be appreciated. Tried putting {} around the formula, as well as typing
gt; it out then pressing CTRL ALT ENTER but maybe doing something wrong. Still
gt; not working
gt;
gt; Ok
gt;
gt; quot;Harlan Grovequot; wrote:
gt;
gt; gt; Chiller wrote...
gt; gt; gt;Hi guys; Ive been using an article on 'how to look up a value in alist and
gt; gt; gt;return multiple corresponding values' from an Excel MVP but using his data
gt; gt; gt;example and his formula doesn't work. I am trying to match values from a
gt; gt; gt;column with a set cell value and return the row numbers one after the other.
gt; gt; gt;I.e if the value 'boots' is in Cells B10, B15 and B20, I want in Cells C1, C2
gt; gt; gt;and C3 the values 10, 15 and 20. He suggests using the following function(s).
gt; gt; gt;
gt; gt; gt;=SMALL(IF($B$1:$B$20=$A$1,ROW($B$1:$B$20)),ROW(1: 1))
gt; gt; gt;
gt; gt; gt;But is does not work, I have found the error is in the if function and a) it
gt; gt; gt;not returning an array for the 'small' function and b)matching multiple
gt; gt; gt;values in B column to $A$1.
gt; gt;
gt; gt; The formula works for me as long as I enter it as an array formula. Are
gt; gt; you entering it as an array formula? Actually, what do you mean by
gt; gt; quot;doesn't workquot;? Returns wrong numeric results? Returns error values?
gt; gt; Excel doesn't accept entry of the formula? Excel crashes when you enter
gt; gt; the formula, smoke boils out of your monitor, and meteorites change
gt; gt; course to pulverize your PC?
gt; gt;
gt; gt;

Chiller wrote...
....
gt;it out then pressing CTRL ALT ENTER but maybe doing something wrong. Still
gt;not working
....

It's [Ctrl] [Shift] [Enter].