How to Search for Values via HLOOKUP or VLOOKUP Functions in Your Excel Worksheet

We will introduce HLOOKUP function which will return the cell value in a column whose column header matches a specific criterion. We will also compare it with VLOOKUP, another similar function but searching vertically.

HLOOKUP function is a seldom mentioned Excel function. Actually this function is a variant of the standard LOOKUP function, and “H” means horizontal. Given a range of tabular data, the function will search in the top row with the specific criterion, and return the value under the matched column.

Let’s see an example.

HLOOKUP Function

In the image below, there are 27 columns in this worksheet.

Now suppose you need to input sales volume of “DataNumen Oracle Recovery” in June into another cell B18 in the worksheet.

Instead of positioning to that cell and copy the value, you can now use the HLOOKUP function in this worksheet.

1. Click the cell where you need to input the value.
2. And then input the formula into the cell:

=HLOOKUP(B17,A1:AA13,7,FALSE)

You need to use four arguments in this function.

• The first is the value that you need to look up. Here we directly use the B17 in the worksheet. You can also input the “DataNumen Oracle Recovery” into the formula. If it is text string, you should also add the double quotes.
• The second argument is the range where the target cell lies in.
• The third argument is the row index. Here we need to find the sales volume in June. And it is in the 7 row in the range. Thus, we will input the number 7 into the formula.
• You need to choose to input “True” or “False” for the last argument. Here we need to find the exact value, and we input the false into the cell.

3. After that, press the button “Enter” on the keyboard. And the result will immediately appear in the cell.

Use HLOOKUP Function in a Range

From the above example, you will know how to use the HLOOKUP function itself. But you can also use this function in a range.

1. Click the cell B18 in the worksheet.
2. And now input this formula into the cell:

=HLOOKUP(B17,$A$1:$AA$13,7,FALSE)

Here we make a small adjustment. We use the absolute reference for the range.

3. And then press the button “Enter” on the keyboard. Thus, the first value will appear in the cell.
4. Now select the cell B18.
5. And then drag the fill handle rightward to fill other cells.

Thus, you will also get the result in other cells.

VLOOKUP Function

The VLOOKUP function is a frequently used function in Excel. You may have already used this function in your worksheet. Different from the HLOOKUP function, here the “V” represents “Vertical”. There are also four arguments in this formula. But the function will search for the target value in the row header of a range and then return the result. This is exactly the difference between the VLOOKUP function and HLOOKUP function. Therefore, when you need to use VLOOKUP function in a range, you should insert the formula in a column instead of a row.

Deal with Damaged Excel Files

Excel files will sometimes be damaged due to various reasons. At this moment, the first thing you need to do is to fix those files. In addition, before you can do it by yourself, you need to make sure that you have figured out the reason. Otherwise, incorrect action can cause permanent damage to your files. If you don’t know what to do with those files, you can turn to a third party tool for help. With the help of this tool, you can repair xlsx data corruption easily.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc document error and outlook repair software products. For more information visit www.datanumen.com