When you create a VLOOKUP or HLOOKUP function, you enter a range of cells, such as D2:F39. That range is called the table_array argument, and an argument is simply a piece of data that a function needs in order to run. In this case, the function searches those cells for the data you're trying to find.
The table_array argument is always the second argument in a VLOOKUP or HLOOKUP function (the first is the value you're trying to find), and the functions won't work without it.
Your first argument, the value you want to find, can be a specific value such as "41" or "smith," or it be a cell reference such as F2. So the first argument can look like this:
=VLOOKUP(F2, ...
The table_array argument always follows the lookup value, like so:
=VLOOKUP(F2,B4:D39, ...
The cell range listed in the table_array argument can use relative or absolute cell references. If you're going to copy your function, you need to use absolute references, like this:
=VLOOKUP(F2,$B$2:BD$39, ...
Also, the cells in the table_array argument can live on another worksheet in your workbook. If they do, the argument includes and the sheet name, and the syntax looks like this:
=VLOOKUP(F2,Sheet2!$C$14:E$42, ...
Make sure you place an exclamation point after the sheet name.
Finally (whew), you enter a third argument, the column that contains the values you're trying to find. This is called the lookup column. In our first example, we used the cell range B4 through D39, which runs across three columns. Let's pretend the values you want to see live in column D, the third column in that range of cells, so the last argument is a 3.
=VLOOKUP(F2,B4:D39,3)
You can use an optional fourth argument, either True or False. Most of the time you'll want to use False.
If you use True or leave the fourth argument blank, the function returns an approximate match to the value in your first argument. So to continue the example, if your first argument is "smith" and you use True, the function will return "Smith," "Smithberg," and so on. But if you use False, the function only returns "Smith," an exact match, and that's what most people want.
To make using True even trickier, if your lookup column—the column you specify in your third argument— isn't sorted in ascending order (A to Z or lowest to highest number), the function may return the wrong result. For more about that, see Look up values with VLOOKUP and other functions.
And for more about the VLOOKUP and HLOOKUP functions, see:
No comments:
Post a Comment