lookup value, return array

  • Thread starter Thread starter Matthew Dyer
  • Start date Start date
M

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?
 
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?
Click to expand...

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.
 
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:D,0)),"",INDEX(A:A,MATCH(ROW(A1),D: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
 
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:D,0)),"",INDEX(A:A,MATCH(ROW(A1),D: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 -
Click to expand...

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!
 
Back
Top