return multiple corresponding values in excel

[Chiller]

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

 

[Kleev]

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

 

[Harlan Grove]

Chiller wrote...

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.

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
"doesn't work"? 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?

 

[Chiller]

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

 

[Kleev]

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

 

[Harlan Grove]

Chiller wrote...
....

it out then pressing CTRL + ALT+ENTER but maybe doing something wrong. Still
not working

....

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