VLOOKUP Excel Formula

A Brief Overview of VLOOKUP

The truth is that, VLOOKUP is difficult yet useful for several applications. Microsoft Excel is not just a simple spreadsheet since it contains numerous built-in functions. VLOOKUP is one of the most useful functions of MS Excel. It searches for a data contained in a lookup table. You can utilize it to search the postal code, a tax value, the cost of an item or even some other often used information.

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 Excel 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 a “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.

Post comment