Array Help

C

Chad

I would like to create an array that is built off of criteria that I specify.
For example, if column A contains numbers and column B contains letters, I
would like to create a formula that would return all of the letters
associated with number 12. Any help would be greatly appreciated.

Thanks in advance,
Chad
 
T

T. Valko

Try this:

Asume numbers in A1:A10
Letters in B1:B10
D1 = number of interest = 12

Enter this formula in E1 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(ROWS($1:1)<=COUNTIF(A$1:A$10,D$1),INDEX(B$1:B$10,SMALL(IF(A$1:A$10=D$1,ROW(A$1:A$10)-MIN(ROW(A$1:A$10))+1),ROWS($1:1))),"")

Copy down until you get blanks

Biff
 
C

Chad

Biff,
Thanks for your quick response but when I drag the formula down, itis
still just returning the first record that meets my requirements (in your
example it would be that the number would be 12). Additionally I am not sure
if that is going to return what I am looking for: I would like to return all
of the data found in column B that matches my criteria in one cell (as an
array). Here is what I have been trying to use but has not been working:

{=INDEX($B$1:$B$8,MATCH(1,($A$1:$A$8=2),0))}

Any suggestions?

Chad
 
T

T. Valko

when I drag the formula down, itis still just returning
the first record that meets my requirements
Click to expand...

It sounds like you have calculation set to manual. Set calculation to
automatic.
I would like to return all of the data found....in one cell
Click to expand...

Depending on how many cells meet the criteria you could use the formula I
suggested and then concatenate those results into a single cell.

For example:

The array formula results are in E1:E5

Then use this formula to concatenate those results:

=E1&","&E2&","&E3&","&E4&","&E5

If that's not acceptable the only other option is a VBA procedure. I can't
help you with that.

Biff
 
Top