Great Benchmark Idea gone Awry.

[jahnosha]

Hi there,

I am driving myself crazy right now as I built a huge project in XLS 2003.
It is broken down into three components:

1) Individual work sheet each of 5 workers has an individual sheet they enter their clients data into.

2) Master Worksheet- pulls all data from #1 spreadsheets as is.

Note: Master worksheet and individual worksheets are set up the same as far as columns, rows and information in those goes.

3) Summary Sheet: Provides the master worksheet info compiled in a different format, as a summary. one column shows the totals of all number 1 worksheets but the second column is a validated drop down list of the individual clients which uses a vlookup function to pull the individual's information from the master.

HOWEVER, among the list of 30 clients or so, two of them start with the letter N but are different altogether, when I pull one or the other it only pulls the information of the first one. basically, VLOOKUP won't distinguish between N1 and N2 clients and just pulls N1 information for both.

This is something I proposed our company uses and now its not working and I don't know why!

Any suggestions are welcome.

 

[sous2817]

Can you post your vlookup formula? Is the last arguement "True"? If so, try setting it to "False" and see if that solves your issue.

 

[jahnosha]

=VLOOKUP($AE$2,'N:\Templates\Ongoing Administration Procedures\Benchmarking\Current Benchmark Data & Process\Master Benchmarking Summary\[Master Benchmarking Summary.xls]Clients over 100 subs All'!$B$11:$BT$53,9)


AE2 is the name of the client to look for, it is looking into the "MASTER BENCHMARKING Summary, the range is all clients, then 9 is the data to pull back into the summary.


Thanks for your help!

 

[sous2817]

Untested shot in the dark, but try:

=VLOOKUP($AE$2,'N:\Templates\Ongoing Administration Procedures\Benchmarking\Current Benchmark Data & Process\Master Benchmarking Summary\[Master Benchmarking Summary.xls]Clients over 100 subs All'!$B$11:$BT$53,9,[B][U]FALSE[/U][/B])

Bold and underline added for emphasis..don't need to do that in your formula.

 

[jahnosha]

Thanks!
It worked, but what does it mean?
Will this affect the lookup of all my other clients?

 

[sous2817]

From the help file:

If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

 

[jahnosha]

Thanks Sous!

 

[sous2817]

Glad I could help and good luck with the rest of your project! If you get stuck again, feel free to post back.

 

[jahnosha]

Likewise.