Microsoft Office Tutorials and References

In Depth Information

**Looking It Up**

The goal is to use VLOOKUP to extract the sales amount for the Wireless Gig

Kit. However, the product names are in the second column of the Sales range,

and VLOOKUP normally searches in the first column. You can use OFFSET

to force VLOOKUP to search for Wireless Gig Kit in the second column

of the range. This is the formula in cell B3: =VLOOKUP(“Wireless Gig

Kit”,OFFSET(Sales,0,1),3, FALSE).

Note that the offset specified as the third argument to VLOOKUP is 3. That’s

because the sales figures are in the third column relative to the Products

column, where VLOOKUP is performing its search.

Here’s how to use either HLOOKUP or VLOOKUP:

1. Click a cell where you want the result to appear.

2. Enter either
=HLOOKUP(
or
=VLOOKUP(
to start the function.

3. If using

•HLOOKUP:Enterthevaluethatyouwanttofindinthetoprow

of the table.

•VLOOKUP:Enterthevaluethatyouwanttofindinthefirst

column of the table.

4. Enter a comma (
,).

5. Enter the range address that defines the table of data, or enter its

name if it has been assigned one.

6. Enter a comma (
,).

7. If using

•HLOOKUP:Enteranumbertoindicatetherowofthevalueto

return.

•VLOOKUP:Enteranumbertoindicatethecolumnofthevalueto

return.

Remember that the number you enter here is relative to the range or

area defined in the second argument.

8. Optionally, enter a comma (
,) and
FALSE
.

This forces the function to find an exact match for the value entered in

the first argument.

9. Type a
), and press Enter.

Excel also provides the LOOKUP function, which is specialized for returning

values from single-column or single-row ranges. See Excel Help for more

information on this function.