Use the table_array argument in a lookup function

When you create a formula using the VLOOKUP or HLOOKUP functions, you enter a table_array argument, which is a range of cells, such as D2:F39. The function searches that range 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 argument is the value you're trying to find). These functions won't work without the table_array argument.

Your first argument can be a specific value like"41" or "smith," or it can be a cell reference, like F2. The first argument would look like this:

=VLOOKUP(F2,...

The table_array argument always follows the lookup value, like this:

=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 formula to other cells, you need to use absolute cell references (note the $ signs), like this:

=VLOOKUP(F2,$B$2:BD$39, ...

If the cells in the table_array argument are on another worksheet in your workbook, the argument should include the sheet name followed by an exclamation point. The syntax would look like this:

=VLOOKUP(F2,Sheet2!$C$14:E$42, ...

The column that has the values you're trying to find is the third argument you’ll enter. This is called the lookup column. If the values you want to see are in column D (the third column in the table_array), the last argument is a 3.

=VLOOKUP(F2,B4:D39,3)

Although it’s optional, it’s recommended that you use a fourth argument, either False or True. Use False if you’re looking for an exact match.

=VLOOKUP(F2,B4:D39,3,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 if your first argument is "smith" and you use True, VLOOKUP returns "Smith," "Smithberg," and so on. But if you use False, it returns only "Smith," an exact match, which is 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), you might get the wrong result.

For more about the VLOOKUP and HLOOKUP functions, see Lookup and reference functions (reference).

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×