Excel Tips and Tricks for Publishers: VLOOKUP & HLOOKUP

This tutorial is part four of the Excel Tips and Tricks for Publishers series. The first tutorial, Excel Basics, can be found here. The second tutorial, Cell Tricks, can be found here. The third tutorial, Graphs & Pivot Tables, can be found here.

VLOOKUP

The VLOOKUP formula is a way for you to simplify data entry when creating multiple sheets and tables using the same data. One way to look at the same data in a different way is to create a pivot table (find a tutorial here). Another way is to use formulas to do the legwork for you, so that you can enter the data you know and let Excel fill in the gaps. You can use this tool to pull information from the sheet you’re working in, to pull from a different sheet in the same document, or even to pull from a completely separate file. This is especially useful if you have a particular ISBN that you want to look at, and perhaps the data changes and you don’t want to update all the sheets where that data is located.

The Formula

The foundation of the VLOOKUP function is the formula, and once you understand how to build that it will be much easier to make it do what you want. This is what the formula consists of:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

In simpler terms:

=VLOOKUP(the thing you know, table to work from, column number to find the answer, FALSE for exact/TRUE for approximate)

In either case, remember there are no spaces between any of the pieces. The below photo is a visual of what each part of the formula means, but they will be explained through the process of using the formula.

Using the Function Tool

To start with the VLOOKUP function, click into the cell to the right of the information you already have. In the example below, we already know the ISBN of a title, and we want to know the price of that particular unit. You can click the fx symbol near the dialogue bar, or under the Formulas tab click Insert Function.

If the VLOOKUP option is not already on the list of options, type it into the search box and click Go. Once you’ve selected VLOOKUP, hit OK.

An extremely handy box will appear with a space for you to select the data for each part of the formula. You have two options here: you can type in each answer or you can click the cell(s) necessary to complete the formula. This box is also useful in that it reminds you what goes in each space, and will warn you if you’ve put incorrect data in so that you know where the problem is. It will also tell you what the answer is going to be, based on your entries, before you close the box.

Lookup Value: In this spot, you are entering the data you already know. Click into the dialogue box titled Lookup_value and on the table you are currently working on, click the cell that has the information entered into it and the information will automatically appear in the box. Alternatively, you can type out the column/row of the cell.

Table Array: This is where you indicate what table to take the original data from. Once you have clicked into the Table_array box, click and drag to select the table with the complete data in it. Again, the right designations will appear in the dialogue box. If you are typing in the formula, make sure to use a colon in between cells. It should look like this: A1:G9. The information from your Lookup Value should always be in the first column of the Table Array.

Column Index Number: This part of the formula tells Excel which column to look in for the information you want. Based on the idea that the information you already have is in the first column that you selected in the Table Array, count out what column number the data you’re looking for is in. In the example below, the ISBNs are under column 1 and we want to know prices, these are in column 4. Again, the information from your Lookup Value should always be in the first column of the Table Array.

In this case, it is not always easiest to click the cell you want for the answer. You could click the specific cell with the answer in it, or you could simply type the column number, which gives you more freedom later on if you want to copy/paste the whole VLOOKUP formula. In this example, we’ve typed the number 4 into the col_index_num box.

Range Lookup: This final box is looking for you to type in either TRUE or FALSE. Typing TRUE means that it can give you an approximate response, which is helpful if you aren’t entirely sure that the information you have is correct. For example, if the information you have for the Lookup Value is an author name that you think may be misspelled, then using TRUE would help so that it will look for something that is the closest match to what you’re looking for.  If you use FALSE in the Range Lookup spot, then it will only provide the information if it exactly matches your data.

By now the Formula Result at the bottom of the pop-up box should have the answer you want ready to go. Double check to make sure it makes sense, and that all of the data in the boxes looks right and does not have any errors and then click OK. As you can see, the formula is complete and the answer you were looking for is in the cell you originally selected.

Copying the Formula

If you are planning to use the formula more than once, then there is a step that is very important before copying the formula. Go back into the Function box by clicking Insert Function, or the small fx box near the formula you created.

In the Table Array box, you are going to enter dollar signs into the formula before each part of the cell designation. This will lock the table array so that no matter where you paste the formula, the Table you have selected will not shift around. You can manually change it by typing $A$1:$G$9 or you can highlight the information entered and hit F4 on your keyboard. This will automatically add the dollar signs.

Once this is done, click OK to close out of the box and then you can copy/paste the formula down the column you are working in, and the only numbers that will shift are the ones in the Lookup Value. Another way to easily copy this formula is to click the small black square on the corner of the cell and drag it down however far you want that formula to go.

Please note that you cannot copy/paste this formula between columns, and if you want to get different information you will have to reselect all of the information for the formula in the new column. You can use the same formula (with locked tables) up and down a column, but not sideways.

Typing the Formula

A few tips and reminders if you decide to type out the formula:

  • Make sure that if you’re typing the formula out there are no spaces between each piece and the commas.
  • In the table array, use a colon [ : ] between the cell distinctions.
  • Don’t forget to use a dollar sign [$] to lock the table so that the formula doesn’t shift when you copy it.
  • Always use FALSE in the range lookup, unless you want an approximate outcome.
  • If you run into any issues with the ISBNs, you may need to reformat them as numbers or take out the dashes. You can find instructions on how to do that here.

Using Different Sheets

If you are building a table in a new sheet and want to pull data from a different sheet on the document you can easily do that! For the example below, the sheet with the answers in it has been renamed to Original (which you can do by right-clicking the sheet tab and selecting Rename). In the Table Array box, add Sheetname! before the usual table array. If you switch sheets to select the table, the sheet name will automatically be added. So in this example it will say Original!A1:G9 or if you want the table locked it will say Original!$A$1:$G$9

The rest of the boxes will stay the same. Excel knows the answers are in the table on the Original sheet, so the Column Index Number does not need to change, you can still just type the number into that box. Once all of your data is there, click OK and everything is there.

Using Different Files

If you are building a table in a document and want information taken from another file, the VLOOKUP function is extremely useful. It helps if the data changes and you don’t want to update every file multiple times, but it also saves you a lot of time.

Similar to how to use a different sheet for your table array, your Table Array section will change slightly to add this before the cell designations:

‘[Workbook name.xlsx]sheet name’!

In the below example, the final formula for Table Array looks like this: ‘[New Workbook.xlsx]Sheet1’!$A$1:$G$9

Again, if you click over to the file with the information you want and select the data in there then the formula will update automatically. But keep an eye out for little details like the placement of the single quotation marks, and the exclamation point. The rest of the boxes can be filled out as usual, with a single number in the Column Index Number, and TRUE/FALSE in the Range Lookup.

Using a different file automatically comes with a locked table array, so you can easily copy this formula and drag it down the column.

HLOOKUPS

If VLOOKUP stands for “Vertical Lookup” specializing in columns, than HLOOKUP is your go-to for horizontal (row) inquiries.

Let’s pretend you need to know how Book 3 preformed throughout the year.

 

ExcelVH1

 

The same VLOOKUP formula applies, we’re simply re-angling its search direction.

 

ExcelVH2

 

When we’re choosing our lookup value, we’re going to be picking the row we expect to find our data in, not the column. We’re changing direction. In this case, we’re looking to see how much was made each month. We want to see how it preformed throughout the year, which is why we include our entire data set within the table_array.

We’re only interested in Book 3, so for our row_index_num, we’re looking at the 4th row (the blank corner counting as row 1). Of course, we only want data that completely matches so we choose “false,” which will give us an exact match.

We close the formula with a bracket and ta da! You are an Excel Master.

ExcelVH3