LOOKUP function help

[Bryan]

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.

 

[Roger Govier]

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 Data>List>Create>my List has headers
XL2007 Insert tab>Table>My tables has headers.

All the necessary filtering in then in place for you.

 

[ryguy7272]

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.