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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My answer doesn't involve VBA and appears to directly answer your following comment regarding combining lookup values for the VLOOKUP function.
ASKER
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.
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.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Client-Name-and-Number-Lookup-TEST.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
What you actually need to enter is the cell reference for the range, which is
=$A$2:$A$11As 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.
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
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
....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.
ASKER
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
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
ASKER
=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?