Data lookup is quite simply the process where values in Excel are scanned until certain results are found. In Excel 2016, there are two main formulas for looking up the data you have in a worksheet. There is VLookup where the V stands for vertical, and there is HLookup where the H stands for Horizontal.
You are watching: How are lookup tables useful to businesses analyzing data in a spreadsheet
The Basic VLookup Formula
Below is the basic VLookup formula.
Inside the brackets, you will see the information that Excel asks us to enter into the formula.
You can also go to the Formulas tab, click the Lookup & Reference button, then select VLookup.
You will then see this dialogue box:
The benefit of using the dialogue box when entering a formula is the bit of instruction that Excel gives you for each field. If you are newer to Excel, it is recommended that you use the Function Arguments dialogue box to create your formulas. It is a lot less trial and error that way.
For the time being, however, let"s look the formula in our worksheet again.
Lookup_value is the value that we want to look up.
The table_array is the table where the data will be retrieved.This value can be a range or the name of a range.
Col-index_num is the column number in the table.The first column in your table is column 1.
Range_Lookup is typically true for false.True will give you a near match.If you want to find an exact match, it is false.
Let"s learn how to use the VLookup formula.
Take a look at our worksheet below.
On the left, we have birthdays listed.We know that 12 means December, and we could use the MONTH function to fill in December for us, but instead we want the text value.
On the right, we have created a lookup table.We are going to use a VLookup to find the text value.
We start by entering the VLOOKUP function, then open brackets.
The first piece of information we must supply is the lookup value.We know the lookup value is the month located in Column A (the first column), but we must use the MONTH function to specify this, as shown below.
Next, we add a comma, then for the table_array, we are going to highlight the cells in the table; however, we do not highlight the column headers.
Enter another comma.
Now, for the col_index_num. Ours is Column 2.We then enter another comma.
Since we want the exact match, we enter FALSE.
Close the brackets, then push Enter.
About Lookup Tables
A lookup table is created by simply creating a table in Excel. It is used as a master list of sorts, and you will use it to locate data that you need to find using search criteria that you will enter into your lookup formula. You can use a huge table of data that you have created as your lookup table.You do not have to create a lookup table just to use either the VLookup or HLookup functions.
For example, let"s say you have a table within a spreadsheet that contains 15,000 customer names, complete with address, billing information, shipping information, anniversary dates, and etc. You can use a lookup formula to find specific data within the table.If you wanted to find the address, for example, you could create a lookup formula to find that within your table of data.
Naming Lookup Table Data
If you are looking up data that may be on different worksheets within a workbook, it makes it easier if you name the data you are going to be searching.
This is easy to do.
Go to the lookup table data.Click anywhere in the data, then press CTRL+A.This selects all of the data.
Next, go to the Formula tab.
Click the Define Name button in the Defined Names group.
Enter a name for the group. The scope is workbook.
Now, when you enter the table_array information into your data lookup, you can simply enter this name.In our case, it is products.You will see it appear in a dropdown menu.
Please note that if you add data to the end of a lookup table after you have named it, then enter the name of the data into Excel (we named ours Products), the formula will not retrieve that data because it is not part of your named data.
We entered another product to our product list below.
Now we can go to our Name box, and choose the name of your data.
As you can see, the last row is not included.
If you add rows or columns to a lookup table after you have named it, insert the data into the existing data table.Do not put it at the very beginning or the very end. Otherwise, you will have to rename it, then change your lookup formula.
Look what happens when we insert the new row above our last row.
It is now included with the data.
However, you can always add extra blank rows in your lookup table so that you have room to add new things at the end.
VLOOKUP:A Real Example
It is important that you understand exactly how VLOOKUP works.For that reason, we are going to show a more realistic example.
Below we have an Excel invoice.This is just one of the pre-built templates that we are going to use.
We also have a product list in another worksheet.
We are going to use VLOOKUP to find the product ID and the price per unit for our invoice.
Take a look at our invoice again.
We have entered in Crab Meat as the first product sold.
Now, we are going to use VLOOKUP to find the product ID.
Enter your VLookup formula in the cell for the product ID.
Notice that we entered in Products for the table_array.
You should now have the product ID in the cell.
Now let"s do the price.
As we fill in our formula, notice that when we get to table_array and type in the name for our data, it appears in a dropdown list.
Finish entering in the formula.
Close the brackets when you are finished entering the formula, then press Enter.
Whereas VLOOKUP is vertical lookup, HLOOKUP is horizontal lookup.However, vertical or horizontal lookups do not refer to the table in which you want to place the results.Instead, it refers to the table in which you are using to search for data.
When you use the HLOOKUP function, you are looking for data horizontally.In VLOOKUP, we looked in a column for data.Columns are vertical.In HLOOKUP, you search rows.Instead of entering the column number, you will enter the row number, as shown below.
Add the end bracket to the formula, then press Enter.
Working with Near Matches
Let"s talk about range lookup in the VLOOKUP and HLOOKUP formulas. As we already have learned, if you enter FALSE as the range lookup, Excel will return the exact match for the results.
However, if you use the TRUE argument for a lookup, it will use the nearest match that is of the next largest value if an exact match is not found.
For our lookup table, we have a grading scale.
We are going to use VLOOKUP to input the letter grade.
Below is our formula.Notice that we named our data "score."
Now, drag the handle in the lower right hand corner of the results cell to fill in the rest of the data.
See more: Can Beggin Strips Cause Red Poop, Beggin Strips Dog Poop Red
When There is Missing Data in a Lookup
If you use VLOOKUP or HLOOKUP, and Excel cannot find the data you are looking for, you will see this message appear in the cell after you press Enter to see results.