Hi everyone!

I've been tackling excel for the past 2 days at work trying to format a
spreadsheet I made that lists accounts associated with different people. I
am trying to write up a lookup function that can look up a certain account
code from a list of account codes and would ultimately return the people
associated with that account number. In this case, let's say that columns A
through F have information regarding the person in question and column G
contains the account numbers. The array in question ranges from A2 to G549.

I essentially need to write a LOOKUP function that searches through column G
for a certain account code, finds them (because there can be multiple
returns), and lists the people that are associated with that account number.
Is there a way that the LOOKUP function can return an array of values instead
of just one value?? Thank you.

Hi Bryan

Why not simply use Autofilter on your data
Use the dropdown on column G to select the Account Number you want, and
you will see all of the relevant detail.

Better still Create a List or Table
XL2003 DataListCreatemy List has headers
XL2007 Insert tabTableMy tables has headers.

All the necessary filtering in then in place for you.
--
Regards
Roger Govier

Bryan wrote:
Hi everyone!

I've been tackling excel for the past 2 days at work trying to format a
spreadsheet I made that lists accounts associated with different people. I
am trying to write up a lookup function that can look up a certain account
code from a list of account codes and would ultimately return the people
associated with that account number. In this case, let's say that columns A
through F have information regarding the person in question and column G
contains the account numbers. The array in question ranges from A2 to G549.

I essentially need to write a LOOKUP function that searches through column G
for a certain account code, finds them (because there can be multiple
returns), and lists the people that are associated with that account number.
Is there a way that the LOOKUP function can return an array of values instead
of just one value?? Thank you.

Access would be better for this. If you're stuck with Excel, consider this
setup:
A B
a 1
b 2
c 3
a 4
b 5
c 6
a 7
b 8
9
10
11
7
8
9
9
16
17
18
19
20

Cell E1 = c
Put this in Cell F1:
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")

Hit Ctrl+Shift+Enter, not just Enter.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Bryan" wrote:

Hi everyone!

I've been tackling excel for the past 2 days at work trying to format a
spreadsheet I made that lists accounts associated with different people. I
am trying to write up a lookup function that can look up a certain account
code from a list of account codes and would ultimately return the people
associated with that account number. In this case, let's say that columns A
through F have information regarding the person in question and column G
contains the account numbers. The array in question ranges from A2 to G549.

I essentially need to write a LOOKUP function that searches through column G
for a certain account code, finds them (because there can be multiple
returns), and lists the people that are associated with that account number.
Is there a way that the LOOKUP function can return an array of values instead
of just one value?? Thank you.