lookup value, return array

[Matthew Dyer]

Hello all. I am trying to figure this out.
I want to fill a list box by using a user-defined-name that returns an
array. The only problem is I cannot for the life of me figure out what
my formula should be to build my array. I want to look in column C and
if "X" is found, return the value of column A. help?

 

[Matthew Dyer]

Hello all. I am trying to figure this out.
I want to fill a list box by using a user-defined-name that returns an
array. The only problem is I cannot for the life of me figure out what
my formula should be to build my array. I want to look in column C and
if "X" is found, return the value of column A. help?

There are multiple instances of "X" in column C and I want my formula
to return all values of column A with the cooresponding "X" in column
C.

 

[Pete_UK]

You could put this in D1 (for example):

=IF(C1="X",COUNTIF(C$1:C1,C1),"")

and copy this down as far as needed to cover your data. Then in F1 you
could have this formula:

=IF(ISNA(MATCH(ROW(A1),D,0)),"",INDEX(A:A,MATCH(ROW(A1),D,0)))

and copy this down until you get blanks, and you should have your list
of A values where C is "X".

Hope this helps.

Pete

 

[Matthew Dyer]

You could put this in D1 (for example):

=IF(C1="X",COUNTIF(C$1:C1,C1),"")

and copy this down as far as needed to cover your data. Then in F1 you
could have this formula:

=IF(ISNA(MATCH(ROW(A1),D,0)),"",INDEX(A:A,MATCH(ROW(A1),D,0)))

and copy this down until you get blanks, and you should have your list
of A values where C is "X".

Hope this helps.

Pete




- Show quoted text -

This works. I was hoping to develop one single formula to use for the
defined name. With your setup I can define my name with the results in
column F and get the desired results. thanks!

 

[Pete_UK]

You're welcome, Matthew - thanks for feeding back.

Pete