Lookup and return multiple Values

Hi!

Here's one way...

Assume the data in sheet1 is in the range A1:B100

On sheet2 in cell A1 you have a drop down list of the departments: Dept A,
Dept B, Dept C etc..

Use this array formula entered using the key combo of CTRL,SHIFT,ENTER to
extract the corresponding data from sheet1 based on the selection made from
the drop down:

=IF(COUNTIF(Sheet1!B$1:B$100,A$1)>=ROWS($1:1),INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!B$1:B$100=A$1,ROW($1:$100)),ROW(1:1))),"")

Copy down to enough cells to cover the maximum number of occurrences of any
single department. For example, if Dept B appears 20 times and that is the
most of any department then copy the formula to at least 20 cells.

Biff

"Neil" <[email protected]> wrote in message
news:[email protected]...
>I have a list of data in a sheet and I want to extract into a new sheet
> values based on a condition . E.g. Column A has 100 rows of data. Column B
> has a grouping e.g. Dept A, Dept B, Dept C etc . I want to extract into
> another sheet any data from column A based on the value in column B (say
> Dept
> B). Could you advise on the best way?

Hi!

Here's one way...

Assume the data in sheet1 is in the range A1:B100

On sheet2 in cell A1 you have a drop down list of the departments: Dept A,
Dept B, Dept C etc..

Use this array formula entered using the key combo of CTRL,SHIFT,ENTER to
extract the corresponding data from sheet1 based on the selection made from
the drop down:

=IF(COUNTIF(Sheet1!B$1:B$100,A$1)>=ROWS($1:1),INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!B$1:B$100=A$1,ROW($1:$100)),ROW(1:1))),"")

Copy down to enough cells to cover the maximum number of occurrences of any
single department. For example, if Dept B appears 20 times and that is the
most of any department then copy the formula to at least 20 cells.

Biff

"Neil" <[email protected]> wrote in message
news:[email protected]...
>I have a list of data in a sheet and I want to extract into a new sheet
> values based on a condition . E.g. Column A has 100 rows of data. Column B
> has a grouping e.g. Dept A, Dept B, Dept C etc . I want to extract into
> another sheet any data from column A based on the value in column B (say
> Dept
> B). Could you advise on the best way?

Hi!

Here's one way...

Assume the data in sheet1 is in the range A1:B100

On sheet2 in cell A1 you have a drop down list of the departments: Dept A,
Dept B, Dept C etc..

Use this array formula entered using the key combo of CTRL,SHIFT,ENTER to
extract the corresponding data from sheet1 based on the selection made from
the drop down:

=IF(COUNTIF(Sheet1!B$1:B$100,A$1)>=ROWS($1:1),INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!B$1:B$100=A$1,ROW($1:$100)),ROW(1:1))),"")

Copy down to enough cells to cover the maximum number of occurrences of any
single department. For example, if Dept B appears 20 times and that is the
most of any department then copy the formula to at least 20 cells.

Biff

"Neil" <[email protected]> wrote in message
news:[email protected]...
>I have a list of data in a sheet and I want to extract into a new sheet
> values based on a condition . E.g. Column A has 100 rows of data. Column B
> has a grouping e.g. Dept A, Dept B, Dept C etc . I want to extract into
> another sheet any data from column A based on the value in column B (say
> Dept
> B). Could you advise on the best way?

Hi!

Here's one way...

Assume the data in sheet1 is in the range A1:B100

On sheet2 in cell A1 you have a drop down list of the departments: Dept A,
Dept B, Dept C etc..

Use this array formula entered using the key combo of CTRL,SHIFT,ENTER to
extract the corresponding data from sheet1 based on the selection made from
the drop down:

=IF(COUNTIF(Sheet1!B$1:B$100,A$1)>=ROWS($1:1),INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!B$1:B$100=A$1,ROW($1:$100)),ROW(1:1))),"")

Copy down to enough cells to cover the maximum number of occurrences of any
single department. For example, if Dept B appears 20 times and that is the
most of any department then copy the formula to at least 20 cells.

Biff

"Neil" <[email protected]> wrote in message
news:[email protected]...
>I have a list of data in a sheet and I want to extract into a new sheet
> values based on a condition . E.g. Column A has 100 rows of data. Column B
> has a grouping e.g. Dept A, Dept B, Dept C etc . I want to extract into
> another sheet any data from column A based on the value in column B (say
> Dept
> B). Could you advise on the best way?

Hi!

Here's one way...

Assume the data in sheet1 is in the range A1:B100

On sheet2 in cell A1 you have a drop down list of the departments: Dept A,
Dept B, Dept C etc..

Use this array formula entered using the key combo of CTRL,SHIFT,ENTER to
extract the corresponding data from sheet1 based on the selection made from
the drop down:

=IF(COUNTIF(Sheet1!B$1:B$100,A$1)>=ROWS($1:1),INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!B$1:B$100=A$1,ROW($1:$100)),ROW(1:1))),"")

Copy down to enough cells to cover the maximum number of occurrences of any
single department. For example, if Dept B appears 20 times and that is the
most of any department then copy the formula to at least 20 cells.

Biff

"Neil" <[email protected]> wrote in message
news:[email protected]...
>I have a list of data in a sheet and I want to extract into a new sheet
> values based on a condition . E.g. Column A has 100 rows of data. Column B
> has a grouping e.g. Dept A, Dept B, Dept C etc . I want to extract into
> another sheet any data from column A based on the value in column B (say
> Dept
> B). Could you advise on the best way?

I have a list of data in a sheet and I want to extract into a new sheet
values based on a condition . E.g. Column A has 100 rows of data. Column B
has a grouping e.g. Dept A, Dept B, Dept C etc . I want to extract into
another sheet any data from column A based on the value in column B (say Dept
B). Could you advise on the best way?

Hi!

Here's one way...

Assume the data in sheet1 is in the range A1:B100

On sheet2 in cell A1 you have a drop down list of the departments: Dept A,
Dept B, Dept C etc..

Use this array formula entered using the key combo of CTRL,SHIFT,ENTER to
extract the corresponding data from sheet1 based on the selection made from
the drop down:

=IF(COUNTIF(Sheet1!B$1:B$100,A$1)>=ROWS($1:1),INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!B$1:B$100=A$1,ROW($1:$100)),ROW(1:1))),"")

Copy down to enough cells to cover the maximum number of occurrences of any
single department. For example, if Dept B appears 20 times and that is the
most of any department then copy the formula to at least 20 cells.

Biff

"Neil" <[email protected]> wrote in message
news:[email protected]...
>I have a list of data in a sheet and I want to extract into a new sheet
> values based on a condition . E.g. Column A has 100 rows of data. Column B
> has a grouping e.g. Dept A, Dept B, Dept C etc . I want to extract into
> another sheet any data from column A based on the value in column B (say
> Dept
> B). Could you advise on the best way?