Spy Journal 3.0 Filtering the web for you

I realised that when I wrote the ISNA function article that I had never written an explanation of VLOOKUP. I want to write up explanations for a umber of other functions in the future like COLUMN, ROW, MATCH and INDEX followed by OFFSET. All these make lots of sense when you use them with the VLOOKUP function. So I though it best to start with this function. I will also say that everything I write about VLOOKUP here applies to HLOOKUP as well. the only difference is the orientation, that is VLOOKUP looks across columns from left to right, and HLOOKUP looks down rows from top to bottom.

The built in Excel Help is very good at helping with this function. However it doesn't point out many of the pitfalls than occur in common use.

The VLOOKUP function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• lookup_value  Required. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A error value.
• table_array  Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.
• col_index_num  Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

If the col_index_num argument is:

• Less than 1, VLOOKUP returns the #VALUE! error value.
• Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

• range_lookup  Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Important: If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

• If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Common Uses of VLOOKUP.

I use VLOOKUP frequently – many times a day and in many different ways. While I am aware that there are limitations to how it works (more on these soon) in the following circumstances it performs well.

Example: I have 2 columns of the same data from two spreadsheets – different versions or different data sources. I want to know if all the data is in both spreadsheets, and if not which data is missing from which sheet. Typical scenarios include account codes from an accounting system, names from a payroll system, lists of suppliers or customers, lists of gas wells from a an engineers data collection sheet etc.

The data may be several hundred or thousand lines long – and it is not time efficient or practical to scroll up and down the two lists comparing them. Sorting them doesn’t necessarily work, and if there are duplicates then looking at the total number in each list isn’t going to help.

Solution: I would copy and paste the two columns of data into column A of two separate sheets in a temporary work book (or maybe work with it in the source files and delete my work afterwards). IMPORTANT: I would then sort each list in ascending order.

In column B of Sheet1 I would make a formula like so: =VLOOKUP(A1,Sheet2!$A:$A,1,FALSE). After filling this down for each item in column A It is very easy to filter out all the #N/A items. These are the items that do not appear in column A of Sheet2.

In Sheet2 Column B I would write a formula like so: =VLOOKUP(A1,Sheet1!$A:$A,1,FALSE). After filling this down for each item in column A It is very easy to filter out all the #N/A items. These are the items that do not appear in column A of Sheet1.

Example: I am trying to balance a set of data that is being summarised from a source set using SUMIF formulas to group the data into some top level values. The sum of these subtotals should equal the sum of the original data.

Eg RAW Sales data totals compared with the sum of the sales data by Salesman suing the SUMIF formula does not equal for some reason. I have checked and every line of raw data has a salesman’s name against it and each unique salesman’s name is being included in the subtotals. Or so I think. In actual fact there is a typo and there are some sales mans names that have an extra space after the name, or maybe a couple of letters transposed. This is a very common error and not easily located in a set of data of several thousand rows. (see a previous post about this problem)

Solution: Assume the salesman’s names from the raw set of data are in column A of Sheet1 and the sales values are in column B. Column C is blank. Column A of Sheet2 is the subtotals of salesman’s names (appearing once) with the SUMIF subtotals in column B.

In column C of Sheet1 I would enter a formula that compares the salesman’s name from column A against the subtotal page on Sheet2 like so:=VLOOKUP(A1,Sheet2!$A:$A,1,FALSE). Once again after filling down for all the data in the raw data sheet, I can filter on this column and any #N/A values will highlight the problems – spelling mistakes, extra spaces, etc.

That’s enough for this week. Next week I will continue on with more on VLOOKUP and HLOOKUP – more scenarios of day to day use of them, and some of the common traps, including why sorting is important.