How to combine Excel’s VLOOKUP() function with a combo box for enhanced searching

The combo box autocomplete feature associated with the search functionality creates a flexible search tool.

Budget planning, spreadsheet on laptop screen

Photo: simpson33, Getty Images / iStockphoto

When you enter data into Microsoft Excel, the AutoComplete feature tries to help. You probably use this feature often – it is convenient, reduces keystrokes for entering data, and helps to avoid typos. It is a quick feature for convenience. Wouldn’t it be great if you could use this autocomplete feature to search for data? Well you can.

I’ll show you how to incorporate this behavior in Excel into a combo box with the VLOOKUP() function. The control will allow you to search by character using its autocomplete behavior, and the associated function will return the corresponding value for each match.

Learn more: Office 365 Consumer pricing and features

I’m using Office 365 (desktop) on Windows 10 64-bit. You can download the .xlsm demo file or work with your own data. The browser version or the Excel list versions will not support this technology.

How to do a basic search in Excel

We want to enter the characters one by one and see the first matching value and interview value. Excel’s autocomplete feature works with data entry. You cannot use it to search an existing column of values ​​- you can use the feature to enter a new value.

We see: 17 Tips to Protect Windows and Mac Computers from Ransomware (Free PDF) (TechRepublic)

Fortunately, the ActiveX combo box control in Excel offers the same autocomplete behavior: when characters are entered, the control matches the first value in the populated list that matches the input characters. This is an easy fix for the first half of the solution, but we still wanted to know more about the matching registry.

For example, let’s say you want to know the unit price of a particular category in the simple data set shown in Figure A. You know the class begins with the letter B, but you can’t remember the exact name of the class. (This example is innovative, but bear with me). With a combo box, matching categories with the letter B is easy. To get the unit price, we will bind the combo box to the VLOOKUP() function. While entering characters, the VLOOKUP() function will return the unit price of the currently matching category. For example, if you enter B, the combo will first match baked goods and mixes and return the unit price of $2.50. If you then enter E (Be), the control will return the drinks and return the unit price of $39.

Figure A

exceldynamicvlookup-a.jpg

We will combine the combo box and the VLOOKUP() function.

Given the actual data, you might expect B to return drinks Because this value occurs before baked goods and mixes in the category column. Don’t worry about this discrepancy now – it will make sense in a bit. In short, the list of controls will be arranged alphabetically even though the data is not. Now, let’s start adding a combo box.

How to add a combo box in Excel

The first step is to include a combo box and populate it with a unique list of category values ​​so that we can take advantage of its autocomplete behavior. Before we add the combo box, let’s create a unique list of category values ​​as follows:

  1. Select the data source, C4: C49.

  2. Click the Data tab, and then click Advanced in the Sort & Filter group.

  3. In the resulting dialog box, click the Copy to another location option. The list range must be correct because you specified it before you started.

  4. Enter F4 as the copy to setting.

  5. Check only unique records option (Figure B) and click OK.

Figure B

exceldynamicvlookup-b.jpg

Copy a unique list of class values.

Figure C Displays the unique list next to the original data source. On a worksheet, you should put this list somewhere out of reach, but for our purposes, it’s convenient to see what’s going on. Before continuing, do a quick sort on the list of unique categories – if prompted, do not expand the sort selection.

Figure C

exceldynamicvlookup-c.jpg

We will populate the combo box list with a unique list of category values.

With a unique alphabetical list available, let’s include a combo box above the dataset. (Enter a few rows about your data if necessary.) Click the Developer tab and do the following:

  1. In the Controls group, click the Insert drop-down menu and choose Combo Box from the ActiveX Controls section.

  2. Include the control in C2 and resize as necessary.

  3. With the control selected, click Properties in the Controls group.

  4. Enter C1 as the LinkedCell setting and F5: F20 as the ListFillRange setting (figure d). In a real sheet, you would probably want to hide the linked cell under control, but I want you to see the values ​​change in real time.

  5. Close the property sheet.

  6. Click the Design Mode option in the Controls group to exit design mode.

figure d

exceldynamicvlookup-d.jpg

Link the combo box to cell C1.

If you select the control and enter B, the control’s autocomplete behavior will return the baked goods and mixes. If you enter C, it will return the Candy control. If you enter Cann, the control will return cann fruits, vegetables, etc. as you see in Figure E, the C1-linked cell, also displays the identical results. Remember that the autocomplete feature evaluates an alphabetical list, not the actual data in the category column.

Figure E

exceldynamicvlookup-e.jpg

The autocomplete behavior returns the first matching value.

We can use the control’s autocomplete behavior to match categories, but we don’t yet know the unit price of the matching category – this is next.

How to use the VLOOKUP() function

We can see that the combo box is linked to cell C1. Now we will add a VLOOKUP() function pointing to C1, and the function will return the unit price of the update value in the combo box. In D2, enter the following function:

= VLOOKUP (1 CAD, C5: D49,2, False)

Before we go any further, let’s quickly review VLOOKUP(). This function uses the following syntax:

VLOOKUP(lookupvalue, tablearray, backbone, [rangelookup])

where look for value is the value it matches; In this case, this is the value in C1; tablearray is the source data that contains the lookup value and the corresponding value you want to return, the column pointer indicates the column in which the returned value is, and Rangeelookup determines whether you want the first closest value or an exact match.

Let’s evaluate an example until all of this is put together. Select the combo box. (If no value is specified in the combo box, the VLOOKUP() function returns #N/A – don’t worry about that now.) C and combo matches Candy Although the first C value in the column is spice. Remember that the list of controls is arranged alphabetically. The first value of the Candy Unit Price is $62.50, as shown in shape and. Next, enter E (Ce) and everything updates to display the first grain The value shown in shape g ($19.45).

shape and

exceldynamicvlookup-f.jpg

The letter C returns Candy’s first unit price value of $62.50.

shape g

exceldynamicvlookup-g.jpg

Enter E and everything updates to the first matching Ce class, grain.

I like this example because there are many class values ​​with the same first few characters.

How to use Quick Scan in Excel

As it is, starting a new search is annoying because you have to clear the search string first. This is a lot of work, so let’s add a small piece of code to do it for you.

First, save the workbook as a macro enabled workbook (xlsm). Then press Alt + F11 to open the Visual Basic Editor (VBE). Use Project Explorer to select the appropriate sheet (this is Sheet 2 (Data) in the demo workbook). Enter the simple double-click action in the format Menu A.

Menu A

Private Sub ComboBox1_DblClick (Cancel ByVal as MSForms.ReturnBoolean)

Clear search combo.

Me.ComboBox1.Value = “”

End Sub

Don’t try to copy the code directly from this webpage – the VBE will complain about web characters it can’t rate. Alternatively, enter it yourself or copy the code from this webpage first into Word or Notepad and then into VBE.

The code is simple: setting the Value property to none clears the control’s text element, removing the previously set value so you can quickly start again. This double-click trick isn’t intuitive, so if you’ve shared the file with other users, you’ll need to tell them.

How to prevent the error in Excel

Currently, when the control text box is empty, the VLOOKUP() function returns an error. You can easily suppress this view using the IFERROR() function as follows:

=IFERROR(VLOOKUP($C$1, C5:D49,2,FALSE),””)

When the control is cleared, the results of the VLOOKUP() function will disappear instead of displaying the error.

How to use Easy Search in Excel

The simple control and function combine to return a corresponding value from a data set. The function is updated as characters are added. When done, double clicking will clear the search string.

Although convenient, it has a limitation: it stops at the first matching value. There is no way to browse for other matching values ​​in the same column.

Stay tuned because over the next few months we will be building more complex search controls.

Send me your questions about Microsoft Office

I answer readers’ questions when I can, but there is no guarantee. Do not send files unless asked to do so; Initial requests for help that arrive with attached files will be deleted as unread. You can submit screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, you likely won’t get the response “Please troubleshoot the workbook and fix the error”, but “Can you tell me why this formula isn’t returning the expected results?” Probably. Please mention the app and version you are using. I do not compensate TechRepublic for my time or expertise when helping readers, nor do I charge a fee from the readers I help. You can contact me at susansalesharkins@gmail.com.

see also