Accessing adjoining records with VLookup

I have a table with about a 1,000 client name and client numbers.  When working in Excel I periodically need to lookup a client number. (See Sample Table Below).

I am using =VLOOKUP(C2, A2..B11,1,TRUE) to get the actual Client Name and
                  =VLOOKUP(C2, A2..B11,2,TRUE) to get the Client Number  

I have two problems.

1. Unless you enter the query the exact client name, you get the client name and client number of the previous client.  I understand this is the way VLookup works. Can that aspect be modified in some way to multiple answers?  

2. There are several incidents of business or individuals with similar names.  Same question as above?

3. We could expand this into looking up TaxID numbers, phone numbers etc. if we could solve this problem.  And please do not tell me that we could do this much easier with a database.  I understand but we are always working in Excel and it would be very convenient.  

The entire office in running Excel 2003

Client Name                       Client Number     Query            Client Answer                Client # Answer
Big City Bakery                   1200                      Valley            The Donut Shoppe       7250
Big City Tires                        1430
Carefree Chiropractic          2010
Mountainview Lodge          5460
Realty Associates                  6980
The Donut Shoppe              7250
Valley Electrical Supply       8810
Valley Feed Store                8820
Valley Plumbing Co.           8830
Valley Supply Company     8840
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
This seems to be very similar to my question at

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28645666.html

Try file at the accepted answer. Insert your own data in the sheet and save the file and then reopen it with macros enabled.
James ElliottManaging DirectorCommented:
You could create a concatenation column which creates a unique value in both your search fields, and lookup data.

An example is attached which demonstrates. Note use of the FALSE flag at the end of the formula in column F, which denotes that a precise match is needed. This stops the formula from guessing.
EE.xls
Bill GoldenExecutive Managing MemberAuthor Commented:
I'm sorry but VBA is just beyond me at this moment in my professional career. Therefore, looking for a simple answer is there a way to add to the value found in C2?

=VLOOKUP(C2, A2..B11,1,TRUE)

In other words, if the value of C2 is actually ABC, can I add the letter Z to the Query or combine the contents of two cells, say C2 and Z2?
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Rob HensonFinance AnalystCommented:
Change the third parameter in the VLOOKUP from TRUE to FALSE. It will then only find an exact match.

You can also use Data Validation in the query cell to look at the list of available values.

Thanks
Rob H

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James ElliottManaging DirectorCommented:
My answer doesn't involve VBA and appears to directly answer your following comment regarding combining lookup values for the VLOOKUP function.
Bill GoldenExecutive Managing MemberAuthor Commented:
The reason I am using the True function instead of False is that entering "Clark County Door & Supply, LLC" is a terrible waste time and invariably someone will enter "Clark County Door AND Supply, LLC" or "Clark County Door & Supply, Inc." or "Clark County Door & Supply Inc." and on and on and on.  And then, there are those folks with just unimaginable last names. Try remembering how to spell Lakhnavi; and generally it is Hernandez, but occasionally it is Hernandes.

This may be the case of trying to insert a square peg into a round hole. A database with an expanded query function might be better.
Rob HensonFinance AnalystCommented:
The Data Validation option will allow users to just select from a list so no worries about misspelt entries.

Data validation doesn't give the option of a reduced list as you type but that can be achieved. How long is your list?

By putting a copy of your list in rows above your input cells and then hide the rows, as you type in the entry cell after the first few letters a list of options will appear.
scsymeCommented:
Hi,

See if the attached is of any use. You can alter the green cells and you get up to ten results. Does not cater for incorrect spelling, but does cater for partial words. Also shows how to implement your third point.

Hope that helps.
Client-contact-lookup.xlsx
Bill GoldenExecutive Managing MemberAuthor Commented:
I understand the principle of data validation, but it seemed to have no effect in this matter.  Therefore, I have loaded a spreadsheet with the test data.  Maybe you can shed some light on what I am doing wrong.  Thanks, Bill
Client-Name-and-Number-Lookup-TEST.xls
FarWestCommented:
if this is a separated sheet and will not be part of a workbook with sheets that will depend on, then I can give you a vbs (not vba inside excel) solution that will open the sheet make advanced lookup for you and put results in the clipboard for example, is this OK for you?
scsymeCommented:
In your spreadsheet you have set the Data Validation to an absolute value: A2..a11
What you actually need to enter is the cell reference for the range, which is
=$A$2:$A$11

Open in new window


As Rob Henson pointed out, this method will not allow you to type a portion of the desired answer and see several matching results, it simply allows you to select the precise value the you are looking for.
Rob HensonFinance AnalystCommented:
On your sample you have put the data validation on the result cells.

I was suggesting you put the Data Validation on the input cell so that it pulls the input value from the name list.

See attached. In B12 you have two options, select from the drop down or manual entry. When typing into the cell, once the entry matches a unique entry from the list above, the remainder of the entry will be shown and pressing enter will accept it.

EDIT:
Actually, 3 options. Right click on input cell and select Pick from Drop down list. This shows entries based on the list above, ie the same list as the Data Validation. Using the + icon in the left margin, you can hide the list.
Client-Name-and-Number-Lookup-TEST.xls
Saqib Husain, SyedEngineerCommented:
....you get the client name and client number of the previous client...Can that aspect be modified in some way to multiple answers?  
The way I see it, your only option is VBA
I'm sorry but VBA is just beyond me at this moment in my professional career.
You would not need any skill in VBA to be able to use a tailormade application. All you need to know is how to get going and it would take only a few minutes to learn it. For any further modification/improvement you have this forum at your disposal.
Bill GoldenExecutive Managing MemberAuthor Commented:
This was the case of just not enough points to spread around.  I want to thank everyone who participated. Unfortunately, I just could not follow some of the examples (my thinking is so two-dimensional). I do want to make a couple of observations, maybe admissions would be more appropriate, and answer a couple of specific questions.
1. I learned a lot about the VLOOKUP feature during this exercise and more specifically its limitations.
2. In a unrelated question I learned how simple VBA is to implement, so that opens up a new world for me.
To answer the questions posed:
1. There are about a 1,000 names in the list an
2. This process will almost always be part of a larger workbook.  
Rob Henson's solution was the best answer. To get around the whole TRUE/FALSE problem, we are installing a series of non-client designations such as A, B, C, etc. This will enable the user to merely type the letter V and then used the drop down menu to find the correct client without having to know the specific nomenclature of the business name or the specific spelling of a person's last name.  It is not the most scientific solution but it works and in an office full of 45-65 year old single/divorced/sole support of the family moms - it will make their life a little easier and improve efficiency.  
I will be posting a series of questions regarding some specific issues as we implement this process.  Hope I get the same great help.  Bill
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.