For those new to Microsoft Excel the Excel VLOOKUP can initially be difficult to understand, yet it is so useful that it is a requirement for anyone that wants to be an Excel consultant or financial analyst must master it. As you probably already know, Microsoft Excel is a powerful spreadsheet application that contains numerous built-in functions, many of which business users should know. The VLOOKUP is one of the most useful functions of MS Excel, and this free Excel help article explains how to use it, with a twist, the twist being the addition of Excel named ranges. The Excel VLookup searches for a data contained in a lookup table, hence the name. You can utilize it to search the postal code, a tax value, the cost of an item or even some other often used information for example. It is so powerful, so useful, and once understood, so easy to use.
The lookup value is located in the left-most column of the lookup table. The lookup table can contain several columns, but the lookup value should always be contained in the first column of the table.
The syntax for the VLOOKUP function is:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Lookup_value – This is the cell that contains the value that is searched for. VLOOKUP works by comparing the value of this cell with the values contained in the lookup table.
Table_array – This is the array of columns and rows which is covered by the lookup table.
Col_index_num – This is the number of the column where the values being searched for can be found.
Range_lookup – This argument is optional. When omitted, VLOOKUP searches for the value which is closest to the lookup_value. If you require an exact value, you need to assign a FALSE value to the range_lookup.
The VLOOKUP function is a very helpful MS Excell function. It permits you to take data from another worksheet that is connected to the data you are entering. It helps in speeding up the entry of data while reducing errors. But this function is useful only for a fixed master list. How about if the worksheet you are working with is increasing or decreasing? For example, you have a list of products. Your current worksheet includes all the products that you stock so that in another worksheet, when you type in the product ID, you can copy the product price and name automatically.
If these are the only products you will every store, or if the catalogue only increases infrequently, then that’s alright. You can do some editing of the formulas and worksheets when needed. However, what if you add products frequently? Luckily, there is an excellent way that you can utilize the VLOOKUP function but with a dynamic range. How you will do that? Read further for more information.
Using Named Ranges in VLOOKUP
Normally, when you utilize the VLOOKUP function, you identify a precise range of data, like =VLOOKUP (A3, A3:C11, 4) where the specified range is from A3 to C11. Then if you are going to add a new product, you are required to make it C12 and so on, every time the list of products is altered.
The first thing to do to simplify this process is to use an Excel “named range”. So, you could use the name “product” instead of using A3:C11. The following are the steps to create a named range.
Select the range of cells that you want to name and from the Name Manager, choose and click Define Name. Then the New Name dialog box will appear.
In the dialog box, enter the name you want to use but remember to check that the range is proper.
After doing these things, your VLOOKUP formula will transform in such a way that it looks like this, VLOOKUP(A3,Product,4). You can simply change the range name in the Name Manager.
How to Make Dynamic Named Ranges
Among the features of Microsoft Excel, named ranges are one of the most powerful, particularly when utilized as the source range for charts, PivotTables or list of controls. However, a problem occurs when the contents of a list alter frequently. It would be difficult to redefine the name of the range every time a record is removed or added to the list. The best answer is to make a range that adjusts automatically based on the number of records contained in the list.
Most probably, you don’t want to go to the Name Manager repeatedly. Maybe, you want the list to correct itself automatically and just go on working. Fortunately, there is a solution for that. You can do that by using the OFFSET function. The OFFSET function requests for a row count, but it would work better by using the COUNTA function. The count will increase every time you add new products, which gives you the dynamic range. This by combining a dynamic named range with your VLookup, you have leveraged the power of Excel. And the good part, you do not need to be an expert Excel consultant to do this, any average business user can do this, you just need to take the time to learn how, and we hope these free Excel help articles get you started. And if that is not enough, if the articles and videos do not enable you to do the Excel programming yourself, contact us, our corporate office is in the Irvine area of Orange County California, near Los Angeles and Long Beach. If you are outside the area, we have developer offices across the country, from California, to Dallas, Florida, New York, and beyond. No matter your location we can provide the expert level Excel consulting and Access programming services your business needs. The call is free, consultations are free, and the work is guaranteed, has been since 2004.