ExcelBanter - Range Lookup

Hi Jose

If it is the result or output that you are more concerned about and not the
method of implementation then I suggest using the Pivot Table Method as it is
the fastest, easiest, efficient and logical one to use!

1. Click 'Data' menu and select 'Pivot Table and Pivot Chart Report' option.
2. Select the range of data including headers if any using a mouse.
3. Click Next.
4. Click the 'Layout' button.
5. Drag and drop all field headers listed on the right over to the empty
area named 'ROW' except the field named 'Leader' as in your given example.
6. Drag and Drop the 'Leader' field over to the empty area named 'COLOMN'.
7. Click Ok
8. Click Next.
9. Click 'Option' button.
10. Uncheck the 'Grand Totals for Colomns' and 'Grand Totals for Rows'
options.
11. Click Ok
12. Choose 'New Excel Sheet' or 'Existing Excel Sheet' radio options as per
interest.
13. Click Finish.
14. The 'Total' coloumn and other field row totals may not be of interest to
you. So select each and choose the 'Hide' option in the 'Pivot Table Floating
Toolbar'.

The final output looks similar to your requirement as follows:

Example Used:

Client Nr Client Nm Exe Leader
103438 cocoa inc Patrick Coach
103438 cocoa inc Patrick Kapil
103438 cocoa inc Patrick Patil
103438 cocoa inc Patrick Patnaik
103438 cocoa inc Patrick Rahul
103438 cocoa inc Patrick Randir
103439 cocoa inc Patrick Rahul
103439 cocoa inc Patrick Patnaik
103439 cocoa inc Patrick Patil
103439 cocoa inc Patrick Kapil

Note: The example used has a different data set to yours. Only the Fields
remain the same.

Output:

Client Nr Client Nm Exe Leader
103438 cocoa inc Patrick Coach
Kapil
Patil
Patnaik
Rahul
Randir
103439 cocoa inc Patrick Kapil
Patil
Patnaik
Rahul

Hope this resolves your important official data conversion needs!

Regards
Philip Jacob

"Jose M via OfficeKB.com" wrote:

I would like to create vlookup to show all the leaders while keeping the
Client NR as the look up value.

Client Nr Client Nm Exec
Leader
103438 E D & F MAN COCOA INC Patrick T. Cahill (blank)
103438 E D & F MAN COCOA INC Patrick T. Cahill (blank)
103438 E D & F MAN COCOA INC Patrick T. Cahill (blank)
103438 E D & F MAN COCOA INC Patrick T. Cahill J.Moet
103438 E D & F MAN COCOA INC Patrick T. Cahill J.Moet
103438 E D & F MAN COCOA INC Patrick T. Cahill J.Moet
103438 E D & F MAN COCOA INC Patrick T. Cahill P. Lynder

The output should read as:

Client Nr Client Nm Exec Leader Leader Leader
103438 E D & F Patrick T. Cahill (blank) J.Moet P. Lynder

Do you have any suggestion... I was just wondering if i need a "if" command.
Please advise.