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.