Prev: Before Cell Exit
Next: Combine 2 spreadsheets by parsing from one to the other
From: Bryan on 13 May 2010 11:49
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.
From: Roger Govier on 13 May 2010 12:14
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.
--
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.
From: ryguy7272 on 13 May 2010 12:37
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.
 | 
Pages: 1
Prev: Before Cell Exit
Next: Combine 2 spreadsheets by parsing from one to the other