VLOOKUP: Vertical Lookup in Excel



VLOOKUP: Vertical Lookup in Excel

Five things you were afraid to know. In Excel, VLOOKUP is probably more feared than Pivot Tables. While the name stands for “Vertical Lookup”, VLOOKUP is most used to merge data from another Excel sheet (or tab) to the one you are currently working.

For example, inventory descriptions may be in one Excel sheet and you need to place them in a column next to the inventory number in the current Excel sheet. Using sales representative details, this article will discuss the ins and outs of using VLOOKUP by way of five helpful tips.

1. The Basic Lookup

Assume your information technology (I.T.) department has just delivered a 1,000+ row data set showing all of the year-to-date (YTD) sales invoices. As you can see in this figure, they provided Rep ID numbers instead of the actual sales representatives’ names.

[pic]

In another sheet, a list is given that maps Rep ID numbers with the names, yet this is not very useful in a separate sheet. Using VLOOKUP, the Rep Name column will be added to each sales invoice using the Rep ID field as the map between the two sheets.

[pic]

In order for VLOOKUP to work, these simple rules must be followed:

a) The left column of the lookup table needs to contain the key field. In this case, that will be the Rep ID field in column H.

b) The values in the key field need to be the same type as the values in your database. In this example, the Rep ID field in both data sets is stored as text, with three digits. This will allow the VLOOKUP to work. Therefore, if the sales invoice register used a numeric 55 as the Rep ID for April Garza, the sales representative sheet must also have a numeric 55 and not a text based 055.

The VLOOKUP requires four arguments:

VLOOKUP(Lookup Value, Table Array, Column Index Number, Range Lookup)

• Lookup Value — This is the rep number that you are trying to find. In the current example, it would be the value in C2.

• Table Array — This is the range that contains the key field in the first column and the lookup values to be returned in the subsequent columns. You will specify this as an absolute reference, such as $H$2:$I$21. The dollar signs ensure that Excel always points to this range, even as the formula is copied down the worksheet.

• Column Index Number — In the current example, a column number of one would return the rep number found in column H and a column number of two would return the rep name.

• Range Lookup — For an exact match, use the value FALSE for the fourth parameter. In example number seven, you will see when to use TRUE.

Using the above arguments, the below formula for cell F2 would be appropriate:

=VLOOKUP(C2,$H$2:$I$21,2,FALSE)

Excel will return the sales rep name:

[pic]

To quickly copy the formula down to all of your rows of data, double click the fill handle (the square dot) in the lower right corner of cell F2. Excel will quickly copy the formula and you will have filled in all of the rep names.

[pic]

2. #N/A values indicate missing values in the lookup table

It is always possible that a new sales rep was hired since last creating the sales rep lookup table. A quick scan of the VLOOKUP column’s results is useful to find the values that returned #N/A. This indicates that the Rep ID from the invoice sheet was not found in the lookup table.

If there are too many records to easily scan, sort the dataset in descending order by the column filled with VLOOKUP. Any error cells will come to the top of the dataset in a descending sort.

In the example, it looks like a new sales rep 199 was recently hired:

[pic]

Tip: When you add the new sales rep to your lookup table, insert them into the middle of the range. Your lookup table does not need to be sorted, and this will prevent you from having to reenter the VLOOKUP formula:

[pic]

3. Prevent #N/A Errors in VLOOKUP

If you want to prevent any #N/A errors in your VLOOKUP, you would have to use a series of functions:

• =IFERROR() will return TRUE if the result of the VLOOKUP is an error. You could also use =ISNA().

• =IF() can be used to test if the ISERROR() returns True. If an error is returned, then specify alternate text to be used, perhaps “Unknown Rep.” If an error is not returned, then you have Excel put in the results of the VLOOKUP.  

The following formula will replace the #N/A errors with alternate text.

=IF(ISERROR(VLOOKUP(C2,RepTable,2,FALSE)),"Unknown Rep",VLOOKUP(C2,RepTable,2,FALSE))

Tip: In Excel 2007, the new =IfError() function simplifies this formula:

=IFERROR(VLOOKUP(C2,RepTable,2,FALSE),"Unknown Rep")

6. Convert your lookup values to be the same type and format as the lookup table.

If none of the VLOOKUPs are working (all are returning a #N/A), it is possible that either the lookup value or the first column of the lookup table are in the wrong format. In this case, the values in column C are numeric and the values in column J are text.

[pic]

Use a function to transform C2 to the proper format and the VLOOKUPs will work:

[pic]

In this case the =TEXT() function converted numeric values to text with three digits. Some other useful functions for transforming data:

=UPPER() will convert text to upper case

=LOWER() will convert text to lower case

=PROPER() will convert text to proper case

=TRIM() will remove leading and trailing spaces from text

=VALUE() will convert numbers stored as text to values.

5. Abandon the Exact Match argument to get a “fuzzy” match.

The fourth argument in the VLOOKUP function is to add “FALSE” which tells Excel to look for an exact match.

Occasionally, you might be trying to find the nearest value within a range of values. Say that your commission plan pays a base rate of one percent. Sales over $5,000 earn 1.1 percent. Sales over $12,500 earn 1.25 percent. Sales over $18,000 earn 1.35 percent. You could set up a special version of a lookup table with the lower limit of each range specified in the first column:

[pic]

You would then change the fourth parameter of the VLOOKUP to TRUE to allow a range lookup. Excel will select the lower value in the range (so for a value of $10,000, it will match to the $5,000 row).

This approach can be used for names, addresses and Excel will do its best to find the closest matching record, just like it did in the range example above.

VLOOKUP is one of Excel’s most powerful features as it allows for less data entry by joining multiple sheets worth of information. Try it the next time you need to compare multiple sheets together or simply to save some data entry.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download