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

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

"Chad" wrote in message
...
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

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. Valko" wrote:

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

"Chad" wrote in message
...
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

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

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

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

"Chad" wrote in message
...
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. Valko" wrote:

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

"Chad" wrote in message
...
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