Dear experts,

I've data in 3 columns. Column A is staff names and Column C is client
names. Please see an example below:

Column A Column C
Peter Spring Co
Peter Tom's Brothers
Joe Brooklyn Investments
Joe World Atlas
Joe Yiu's Restaurant

In another worksheet cells A1 & B1, I want to use for VLOOKUP. In cell A1, I
input "Peter". In cell B1, I input: =VLOOKUP($A$1,'SALES'!A2:C6,3), it
returns "Tom's Brothers" only. What I want is when I input "Peter" in cell
A1, in cell B1, Peter's two clients' names can appear in a drop-down menu for
me to choose. Is it possible? If yes, please teach me how to do it.

Thanks in advance.

Assume this table is on sheet1 in the range A2:C6 -

Peter Spring Co
Peter Tom's Brothers
Joe Brooklyn Investments
Joe World Atlas
Joe Yiu's Restaurant

Sheet2 A1 is where you enter the persons name:

Sheet2A1 = Joe

Create this named formula:

Goto the menu InsertNameDefine
Name: List
Refers to:

=OFFSET(Sheet1!$A$2,MATCH(Sheet2!$A$1,Sheet1!$A$2: $A$6,0)-1,2,COUNTIF(Sheet1!$A$2:$A$6,Sheet2!$A$1))

OK

Select sheet2 cell B1
Goto the menu DataValidation
Allow: List
Source: =List
OK

Biff

"Freshman" wrote in message
...
Dear experts,

I've data in 3 columns. Column A is staff names and Column C is client
names. Please see an example below:

Column A Column C
Peter Spring Co
Peter Tom's Brothers
Joe Brooklyn Investments
Joe World Atlas
Joe Yiu's Restaurant

In another worksheet cells A1 & B1, I want to use for VLOOKUP. In cell A1,
I
input "Peter". In cell B1, I input: =VLOOKUP($A$1,'SALES'!A2:C6,3), it
returns "Tom's Brothers" only. What I want is when I input "Peter" in cell
A1, in cell B1, Peter's two clients' names can appear in a drop-down menu
for
me to choose. Is it possible? If yes, please teach me how to do it.

Thanks in advance.

Hi Biff

Your working function works nicely. Thanks.

"Biff" wrote:

Assume this table is on sheet1 in the range A2:C6 -

Peter Spring Co
Peter Tom's Brothers
Joe Brooklyn Investments
Joe World Atlas
Joe Yiu's Restaurant

Sheet2 A1 is where you enter the persons name:

Sheet2A1 = Joe

Create this named formula:

Goto the menu InsertNameDefine
Name: List
Refers to:

=OFFSET(Sheet1!$A$2,MATCH(Sheet2!$A$1,Sheet1!$A$2: $A$6,0)-1,2,COUNTIF(Sheet1!$A$2:$A$6,Sheet2!$A$1))

OK

Select sheet2 cell B1
Goto the menu DataValidation
Allow: List
Source: =List
OK

Biff

"Freshman" wrote in message
...
Dear experts,

I've data in 3 columns. Column A is staff names and Column C is client
names. Please see an example below:

Column A Column C
Peter Spring Co
Peter Tom's Brothers
Joe Brooklyn Investments
Joe World Atlas
Joe Yiu's Restaurant

In another worksheet cells A1 & B1, I want to use for VLOOKUP. In cell A1,
I
input "Peter". In cell B1, I input: =VLOOKUP($A$1,'SALES'!A2:C6,3), it
returns "Tom's Brothers" only. What I want is when I input "Peter" in cell
A1, in cell B1, Peter's two clients' names can appear in a drop-down menu
for
me to choose. Is it possible? If yes, please teach me how to do it.

Thanks in advance.

You're welcome. Thanks for the feedback!

Biff

"Freshman" wrote in message
...
Hi Biff

Your working function works nicely. Thanks.

"Biff" wrote:

Assume this table is on sheet1 in the range A2:C6 -

Peter Spring Co
Peter Tom's Brothers
Joe Brooklyn Investments
Joe World Atlas
Joe Yiu's Restaurant

Sheet2 A1 is where you enter the persons name:

Sheet2A1 = Joe

Create this named formula:

Goto the menu InsertNameDefine
Name: List
Refers to:

=OFFSET(Sheet1!$A$2,MATCH(Sheet2!$A$1,Sheet1!$A$2: $A$6,0)-1,2,COUNTIF(Sheet1!$A$2:$A$6,Sheet2!$A$1))

OK

Select sheet2 cell B1
Goto the menu DataValidation
Allow: List
Source: =List
OK

Biff

"Freshman" wrote in message
...
Dear experts,

I've data in 3 columns. Column A is staff names and Column C is client
names. Please see an example below:

Column A Column C
Peter Spring Co
Peter Tom's Brothers
Joe Brooklyn Investments
Joe World Atlas
Joe Yiu's Restaurant

In another worksheet cells A1 & B1, I want to use for VLOOKUP. In cell
A1,
I
input "Peter". In cell B1, I input: =VLOOKUP($A$1,'SALES'!A2:C6,3), it
returns "Tom's Brothers" only. What I want is when I input "Peter" in
cell
A1, in cell B1, Peter's two clients' names can appear in a drop-down
menu
for
me to choose. Is it possible? If yes, please teach me how to do it.

Thanks in advance.