VLOOKUP Function

[Freshman]

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.

 

[Biff]

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 Insert>Name>Define
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 Data>Validation
Allow: List
Source: =List
OK

Biff

 

[Freshman]

Hi Biff

Your working function works nicely. Thanks.

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Hi Biff

Your working function works nicely. Thanks.