Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

User Input Lookup Table

0

Is it possible to have a user input area to type any query that would match my look up table on tab "look up table" column A and would return the value in Column B. 

On the "User Input" tab I used a vlookup on A2 to return a value in B2 on the Look Up table tab. My question is would it better to have a partial match in my formula to match anything close to that entry? 

I want something easy for a User to be able to know where to type and where the returned answer will return. 

Workbook name "Look Up Table Input"

Thank you so much for all your help!

excelformulas-functions
MS Office Version: Sroncey21
posted by: Sroncey21 (rep: 66) on Fri Dec 7, 2018 at 5:09 pm
Answer
Discuss

Answers

0
Selected Answer

Partial matches seem to imply a high risk of error, meaning the user's entry isn't found. Therefore I suggest to use one or several validation drop-downs from which the user can select and every selection is successful. The attached workbook demonstrates a single drop-down where the list is extracted from the Lookup Table with this formula.

=OFFSET('Lookup Table'!$A$2,0,0,COUNTA('Lookup Table'!$A$2:$A$200))

Note that the size of the table is dynamic but doesn't permit blank cells in the table. If you sort the table, the drop-down list will be sorted, too - automatically.

If the drop-down list is too long you can shorten it by dividing the lookup values into categories and then create separate tables for each catagory. The user would then select a catagory from a first drop-down. That would let a second drop-down be filled with only the values of the list for the selected category.

posted by: Variatus (rep: 4889) on Fri Dec 7, 2018 at 10:44 pm
Discuss

Discussion

Where do I place that formula?

And will this make the user of this sheet able to type in keywords that will match to my lookup table? 
Sroncey21 (rep: 66) Dec 8, '18 at 12:05 am
C'mon! You should at least take the time to read my answer and look at the workbook I prepared for you.
I'm suggesting not to type but to select. The formula drives the drop-down from which you can select. It isn't what you wanted but may serve your purpose. Look at the example in the workbook.
Variatus (rep: 4889) Dec 8, '18 at 1:05 am
I read it. I even tried using it and in the user input tab the dropdown wasn't working. After I even tried placing that formula. 

I really need something that someone can type in their query to do a matched return. I'm thinking using an if true false formula next to each entry on the lookup table would be useful because their may be several matches it could possibly return giving less options to narrow down. 

I attached an edit to your sheet where in column C I created a true, false match for the user input. Is there anyway to make that formula more improved so that if someone misspells something it still returns the partial match to narrow down their optons. Or is there a way to create a drop down based on the partial match. This would be so, so useful. Thank you so so much.

I apologize greatly if I didnt explain what I needed to begin but I did the best I could as I'm working through what would be best for a new user.
Sroncey21 (rep: 66) Dec 8, '18 at 4:38 pm
First off, sorry my example didn't work. It seems that the validation dropdown is emasculated when I save the workbook in compatibility mode. It stops working even on my PC after I close and re-open it. Therefore the example is now in xlsx format. Please try to convert it at your end.
As an alternative, create a data validation dropdown in your Excel 2003. In the Allow field select List and enter the formula in the Source field. 
I just remembered that the list range can't be on another sheet in Excel 2003. Therefore I moved the list to the User Input sheet and attached a second workbook with this arrangement. I tested the download and it worked for me. Note that the column could be hidden. I also seem to remember now that a range from another sheet could be used if it was named. If you decide to go for this kind of solution there are many ways to solve the problem.
Variatus (rep: 4889) Dec 8, '18 at 8:45 pm
After studying your suggestion I still think that my idea of 2 successive dropdowns is your best bet if the single dropdown is too unwieldy.
Your idea would work with a formula like this =VLOOKUP("*"&TRIM(A2)&"*",'Lookup Table'!A:B,2,0) but consider reducing the size of the search range. Its drawback is that it will only return the first match. =COUNTIF('Lookup Table'!A:A,"*"&TRIM(A2)&"*") would give you the number of matches. You might then have a number of reply fields ('User Input'!B:B), say 10, and the 11th just showing "& more: narrow your search criterion". If there are 2 matches, as for "Birth", the other 9 stay blank. Each of the 11 fields would have its own formula constructed on the example you will find at this site.
That may sound good on the surface but you would need an array formula which Excel introduced only in 2007. Therefore you would need to resort to VBA which leaves the 2-dropdown solution easier and more efficient.
Variatus (rep: 4889) Dec 8, '18 at 9:15 pm
I took your advice and looked up how to do an array formula and I think I did it!!

Now, my formula only does EXACT matches to my table. Is there a way to edit the formula I have for the entries in B2 to give me a range of partial matches in G2 decending down? I also want to be able to lock everything down except the look up value in B2. I am so close to getting this together and could not have done it without you!! Worksheet name "TXL 181208 Lookup Input (2).xls"  
Sroncey21 (rep: 66) Dec 10, '18 at 2:31 pm
It seems that you first selected my answr and then changed your mind. Fair enough. However, we try to avoid having Answers in the Discussions because that makes things difficult to find. If you need any more help with your array formula please start a new thread about that.
BTW, congratulations on your success so far! Well done!!! :-)
Variatus (rep: 4889) Dec 10, '18 at 7:27 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login