how to use vlookup in excel 2010 from a different workbook

#HOW TO USE VLOOKUP IN EXCEL 2010 FROM A DIFFERENT WORKBOOK HOW TO#
#HOW TO USE VLOOKUP IN EXCEL 2010 FROM A DIFFERENT WORKBOOK WINDOWS 10#
#HOW TO USE VLOOKUP IN EXCEL 2010 FROM A DIFFERENT WORKBOOK WINDOWS#

If you enter C, the control will return Candy. If you select the control and enter B, the control’s autocomplete behavior will return Baked Goods & Mixes. In a real sheet, you’d probably want to hide the linked cell under the control, but I want you to see the values change in real time.Ĭlick the Design Mode option in the Controls group to exit design mode. With the control selected, click Properties in the Controls group.Įnter C1 as the LinkedCell setting and F5:F20 as the ListFillRange setting ( Figure D). In the Controls group, click the Insert dropdown and then choose Combo Box from the ActiveX Controls section.Įmbed the control over C2 and resize as necessary. (Insert a few rows about your data if necessary.) Click the Developer tab and then do the following: With a unique alphabetized list available, let’s embed a combo box above the data set. Before you continue, run a quick sort on the unique list of categories-if prompted to, do not expand the sort selection.įigure C We’ll populate a combo box list with a unique list of category values. In a working sheet, you should put this list in an out-of-the-way spot, but for our purposes, it’s convenient to see what’s going on. The List range should be correct because you selected it before you started.Ĭheck the Unique Records Only option ( Figure B) and click OK.įigure B Copy a unique list of category values.įigure C shows the unique list next to the original data source. In the resulting dialog, click the Copy To Another Location option. Before we add the combo box, let’s create a unique list of category values as follows:Ĭlick the Data tab and then click Advanced in the Sort & Filter group. The first step is to embed a combo box and populate it with a unique list of category values so we can take advantage of its autocomplete behavior. Now, let’s get started by adding a combo box. In a nutshell, the control’s list will be alphabetized even though the data isn’t. Don’t worry about that discrepancy now–it’ll make sense in a bit. Looking at the actual data, you might expect B to return Beverages because that value occurs before Baked Goods & Mixes in the category column. If you then enter E (Be), the control returns Beverages and returns the unit price of $39.įigure A We’ll combine a combo box and the VLOOKUP() function. For example, if you enter B, the combo will first match Baked Goods & Mixes and return the unit price of $2.50. As you enter characters, the VLOOKUP() function will return the currently matched category’s unit price. To get the unit price, we’ll link the combo box to a VLOOKUP() function. (The example is contrived, but bear with me.) Using the combo box, matching categories to the letter B is easy. You know the category starts with the letter B, but you can’t remember the exact category name. That’s an easy fix to the first half of the solution, but we still want to know more about the matching record.įor instance, let’s suppose you want to know the unit price for a specific category in the simple data set shown in Figure A.

#HOW TO USE VLOOKUP IN EXCEL 2010 FROM A DIFFERENT WORKBOOK WINDOWS#

SEE: 17 tips for protecting Windows computers and Macs from ransomware (free PDF) (TechRepublic)įortunately, Excel’s ActiveX combo box control offers the same autocomplete behavior: As you enter characters, the control matches the first value in the populated list that matches the input characters. You can’t use it to search an existing column of values–you use the feature to enter a new value. Excel’s autocomplete feature works with data input. We want to enter characters one at a time and see the first matching value and a corresponding value.

Neither the browser version nor menu versions of Excel will support this technique.

#HOW TO USE VLOOKUP IN EXCEL 2010 FROM A DIFFERENT WORKBOOK WINDOWS 10#

I’m using Office 365 (desktop) on a Windows 10 64-bit system. LEARN MORE: Office 365 Consumer pricing and features The control will let you search by character using its autocomplete behavior, and the linked function will return a corresponding value for each match.

#HOW TO USE VLOOKUP IN EXCEL 2010 FROM A DIFFERENT WORKBOOK HOW TO#

I’ll show you how to combine this behavior in Excel in a combo box with a VLOOKUP() function. Wouldn’t it be great if you could use this autocompleting feature to search for data? Well, you can. It’s an on-the-fly feature of convenience. You probably use this feature a lot-it’s convenient, reduces data entry keystrokes, and helps avoid typos. When entering data in Microsoft Excel, an autocomplete feature attempts to help. Windows 11: Tips on installation, security and more (free PDF) Windows 11 cheat sheet: Everything you need to know Plan for a Windows 10/11 reinstall by following these steps Image: simpson33, Getty Images/iStockphoto Windows: Must-read coverageġ0 secret Microsoft-specific keyboard shortcuts in Windows 11 How to combine Excel’s VLOOKUP() function with a combo box for enhanced searchingĪ combo box's autocomplete feature linked to a lookup function creates a flexible search tool.

How to use vlookup in excel 2010 from a different workbook