Understanding VLOOKUP
I’ll first explain the VLOOKUP function in Excel, which has four inputs, also known as arguments:
- Lookup_Value – Think of this as what to look for within your data. When analyzing QuickBooks data, the Lookup_Value might be an account number, vendor name, customer name, inventory item, and so on.
- Table_Array – I often describe this argument as where to look. If we’re merging two QuickBooks reports together, then where to look will be the columns of data within the second report. Keep in mind that your Lookup_Value must always appear in the first column of the Table_Array. Typically, the table array will be comprised of two or more columns.
- Col_Index_Num – As noted above, VLOOKUP will look for the Lookup_Value in the first column of the table array. When VLOOKUP finds a match, it then will return a piece of information from a column to the right. Thus, the Col_Index_Num is how we tell Excel which column contains the information we want to return.
- Range_Lookup – This oddly named argument is used to indicate whether we want to return an exact match or an approximate match. When merging two QuickBooks reports, invariably, we’ll want to return an exact match. Users typically type “FALSE” here to signify an exact match, or “TRUE” when seeking an approximate match. A common way I explain approximate matches is when we want to determine which income
tax bracket a taxpayer falls into. When matching data from a QuickBooks report, you’ll generally always be seeking an exact match.
Now that we have a sense of VLOOKUP’s arguments, let’s use this function to combine data from two reports in QuickBooks:
- Inventory Stock Status by Vendor report – As shown in Figure 1, this report provides reorder quantities. However, as shown in Figure 2, the Modify Report window doesn’t give us the ability to incorporate vendor contact information.
Figure 1: The Inventory Stock Status by Vendor report in Excel
Figure 2: The Modify Report window.
- Vendor Phone List report – As shown in Figure 3, this report lists vendors and their main phone numbers. We’ll add their main email addresses to this report as well.
Figure 3: The Vendor Phone List report.
Generating the Reports
Let’s first send the Inventory Stock Status by Vendor report to Excel:
- In QuickBooks, select Reports, Inventory, and then Inventory Stock Status by Vendor.
- Click Excel (or Export in QuickBooks 2011 and earlier), then Create New Worksheet.
- Click the Export button to send the report to Excel.
- Double-click on Sheet1 and type “Report” to rename the worksheet.
Next, we’ll send the Vendor Phone List report to Excel:
- In QuickBooks, select Reports, Vendors and Payables, and then Vendor Phone List. Alternatively, you can select Reports, List, and then Vendor Phone List.
- Click the Customize Report (Modify Report in QuickBooks 2011 and earlier) button. Then, as shown in Figure 4, choose Main Email from the Columns list on the Display tab and click OK.
- Click Excel (or Export in QuickBooks 2011 and earlier), then Create New Workbook.
- Click the Export button to send the report to Excel.
- Double-click on Sheet1 in Excel and type “Contacts” to rename the worksheet. Assigning a short name like Contacts to this worksheet will greatly simplify the formulas that we’ll be writing in a moment.
Figure 4: You can customize a report before exporting it to Excel.
Now that the reports are in Excel, our work will be easier if we combine the reports into a single workbook, as shown in Figure 5:
- Right-click on the Contacts worksheet tab that contains the Vendor Phone List report. If your workbook doesn’t have a Contacts worksheet yet, you most likely skipped a step above.
- Select Move or Copy, choose the workbook that contains the Inventory Stock Status by Vendor report, and then double-click on Move to End.
Figure 5: Both reports are now in the same workbook.
At this point, we could add our VLOOKUP formulas, but we’ll carry out one more step that will make the formulas easier to write, as shown in Figure 6:
- Select View (Window in Excel 2003), then click New Window.
- Select View (Window in Excel 2003), Arrange All, check the Windows of active workbook checkbox, and then double-click on Horizontal.
- Click on the Report tab in the top window, so that the Inventory Stock Status by Vendor Report appears in the top half of the screen and the Vendor Phone List appears in the bottom half of the screen.
Figure 6: Both reports can now be viewed simultaneously.
We’re now ready to build out the formulas, as shown in Figure 7:
- In cell P1 of the Inventory Stock Status report worksheet, type the words “Phone Number.”
- In cell P2 of that worksheet, enter this formula:
=VLOOKUP(A2,Contacts!B:D,2,FALSE)
The phone number for this vendor should appear. If you see “#REF!,” “#NAME?,” or “#N/A” instead of a telephone number, the formula wasn’t written correctly. If you see a zero (0), then that vendor doesn’t have a phone number listed. Once you add a phone number for that vendor on the Contacts tab, you’ll then see it appear on the Reports tab instead of zero (0).
Figure 7: The phone number for the vendor appears by using a simple VLOOKUP formula.
- Now let’s deconstruct the formula:
- Lookup_value – Cell A2 contains “A Cheung Limited.”
- Table_Array – Our Vendor Phone List report appears in columns B through D of the Contacts worksheet.
- Col_Index_Num – The number 2 signifies that we want to return data from the second column of our Table_Array, or in this case column C of the Contacts worksheet.
- Range_Lookup – The word “FALSE” signifies that we want an exact match. This means if A Cheung Limited didn’t appear in column B of the Contacts worksheet, then VLOOKUP would return #N/A.
- Copy and paste the formula down column P to the bottom of the report.
- For rows where column A is blank, VLOOKUP will return #N/A. This signifies that VLOOKUP could not find a match. There are at least four ways to eliminate these #N/A errors:
1. Adjust the formula to look like this:
=IF(A2=”",”",VLOOKUP(A2,Contacts!B:D,2,0))
However, this approach will still return #N/A if the words “No Vendor” appear in column A of your Inventory Stock Status by Vendor report2.
2. If you’re using Excel 2007 or later, you can use this formula:
=IFERROR(VLOOKUP(A2,Contacts!B:D,2,0),””)
Important: The IFERROR function will return #NAME? in Excel 2003 and earlier.
3. If you’re using Excel 2013, you can use this formula:
=IFNA(VLOOKUP(A2,Contacts!B:D,2,0),””)
Important: IFNA will return #NAME? in Excel 2010 and earlier.
4. If you’re using Excel 2003 or earlier, or require compatibility with that version, use this approach (it will work in any version of Excel):
=IF(ISERROR(VLOOKUP(A2,Contacts!B:D,2,0),””, VLOOKUP(A2,Contacts!B:D,2,0))
Next, in cell Q1, enter the words “Email Address,”as shown in Figure 8, then in cell Q2, use this formula:
= VLOOKUP(A2,Contacts!B:D,3,0)
Notice this time I used a zero (0) in place of FALSE. Zero and FALSE can be used interchangeably to signify exact matches in Excel.
Figure 8: The email address for the vendor now appears in cell Q2.
You’ll need to use your choice of IF, IFERROR, IFNA, or ISERROR as shown above to eliminate #N/A errors, but rather than repeat those instructions again, I’m instead going to demonstrate how to make the email addresses be clickable links:
The first step is to add “MAILTO:” in front of our VLOOKUP formula. Amend the formula in cell Q2 to take this form:
= “MAILTO:”&VLOOKUP(A2,Contacts!B:D,3,0)
Next, wrap the formula inside the HYPERLINK function:
= HYPERLINK(“MAILTO:”&VLOOKUP(A2,Contacts!B:D,3,0))
As shown in Figure 9, a clickable hyperlink will appear in cell Q2 of the Report worksheet. In this instance, we’ve only used one of HYPERLINK’s two arguments:
- Link_Location – This is the address of a website or, in this case, an email address. An email address by itself won’t become clickable, but adding “MAILTO:” before the address does make it clickable in Excel. You can type this as “MAILTO:”, “mailto:”, or “MailTo:” – the resulting link you’re building is not case-sensitive.
- Friendly_Name – This optional argument allows you to specify alternate text to display within the worksheet cell.
Figure 9: The email address is now a clickable hyperlink.
If we want to create a clickable link that only displays the email address itself, we can populate the Friendly_Name with a second instance of VLOOKUP:
=HYPERLINK(“mailto:”&VLOOKUP(A2,Contacts!B:D,3,0),LEFT(VLOOKUP(A2,Contacts!B:D,3,0),100))
Not only did I add VLOOKUP in a second time, but I also wrapped the second VLOOKUP function in the LEFT function. If a vendor doesn’t have an email address, the cell will appear blank, otherwise Excel would return a zero. The LEFT function has two arguments:
- Text – In this case VLOOKUP returns the text we wish to display.
- Num_Chars – This signifies the number of characters to return, starting from the left. If I specified 5 here, I’d see the first 5 characters of the email address. I chose 100 in this case as an arbitrarily large enough number to ensure that email addresses don’t get truncated.
To finish off the formula, utilize one of the IF approaches described above, such as IFERROR:
=IFERROR(HYPERLINK(“mailto:”&VLOOKUP(A2,Contacts!B:D,3,0),LEFT(VLOOKUP(A2,Contacts!B:D,3,0),100)),”")
Now when you click an email address, a blank email will appear on your screen, with the To field already populated with your vendor’s email address.
If you find this approach helpful, it’s easy to avoid recreating these formulas each time you export your reports. To streamline the process, export the two reports to comma-separated value (CSV) files instead. Create a new workbook with two worksheets and use the steps described in the Automation Opportunities section of my article “CSV Secrets: QuickBooks Reports to Excel” to link each report to its own worksheet. Once you put the formulas in place in the columns to the right of your QuickBooks data, your merged reports will be self-updating and interactive with clickable email addresses.