How do you use hlookup

If there are spaces in the sheet name, you will need to change the formula further. Let's assume that the table is on a Sheet called "Test Sheet" in the range A1:G3, we would need to modify our formula as follows:. Now it is important for us to mention one more mistake that is commonly made. This will return the right answer, but what happens when you copy the formula to another cell? The table range will be adjusted by Excel and change relative to where you paste the new formula.

Let's explain further Since your table is found in the range A1:G3 and not B2:H4, your formula would return erroneous results in cell K2. To ensure that your range is not changed, try referencing your table range using absolute referencing as follows:. This is a great way to spruce up your spreadsheet so that you don't see traditional Excel errors.

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. Please re-enable javascript in your browser settings.

Data Type Conv. The first row is 1. This number can be different from the worksheet row number, if the lookup table does not start in row 1 on the worksheet. There are three options for entering this argument:. For better results, try these suggestions:. In this example, we'll find the sales total for a selected region. We want an exact match for the Region name, so the following settings are used:.

In this example, rates change at the start of each quarter, and those dates are entered as column headings. In this example:. The lookup value in this example is March 15th. That date is not in the date row, so the value for January 1st 0. For example:. The more you tell us the more we can help.

Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information.

Not enough pictures. Any additional feedback? Submit feedback.