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

"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.

Thanks