Question

Excel: HLOOKUP function - this is how it works

Answer 1

What is the syntax of the HLOOKUP formula?
How to use HLOOKUP in Excel
1st step:
2nd step:
3rd step:
4th step:
5th step:
6th step:
7th step:
quick start Guide

---

HLOOKUP is a handy search function in Excel. Would you like to know more about it? Then read this article..

The Excel function HLOOKUP can be used to look up a value from a search range. The "W" in HLOOKUP stands for horizontal and refers to the first line of a table. This means that matching values ​​for the same column can be found quickly in large tables. The formula syntax of the Excel function and a suitable example are shown step-by-step in this article.

• What is the syntax of the HLOOKUP formula?
• How to use HLOOKUP in Excel

What is the syntax of the HLOOKUP formula?

The horizontal reference in Excel looks like this:

=WVERWEIS(Suchkriterium;Matrix;Zeilenindex;[Bereich_Verweis])

HLOOKUP searches for matching values ​​in the same column. For this you need the above parameters.

• Search criterion: The criterion to be found in the first line of the table. This can be a value, a reference or a character string. The search criterion is required.
• Matrix: Here you enter the area in which Excel should search for the search criterion. The matrix is ​​also required.
• Line index: The line index is the line in which the result is located. If, for example, the result is in the third row of the matrix, the row index 3 must be specified here.
• [Range_Verweis]: Here you have to indicate whether the searched result should only roughly match the search term (TRUE) or completely match (FALSE).

Note: The formats of matrix and search field must match - that means value to value or text to text. The English term for the function is HLOOKUP, by the way. For VLOOKUP, use VLOOKUP.

How to use HLOOKUP in Excel

We have created a sample table that contains item numbers and the associated prices and stocks. The aim is for the article number to be entered using the HLOOKUP function and for Excel to automatically output the price and the stock to us. Suppose the table contains more than 1000 articles - searching manually would be far too time-consuming. The HLOOKUP makes our task easier here by searching the columns for the price or stock after entering the search criterion ( i.e. the article number) and outputs this to us. Follow our step-by-step instructions or take a look at the brief instructions ..

1st step:

First open the Excel document with the entries to be searched for.

2nd step:

You can of course also enter the formula manually - but it is more convenient via the Excel menu. We select cell B5 and go via " Formulas " to " Look up and reference" and finally to " HLOOKUP ".

3rd step:

We want to search for "heise05" in our example. The search criterion we have previously defined in the cell A5. In the " Function Arguments" window, click the up arrow next to Search Criteria. Then click on the cell with the criterion and confirm with [ Enter ].

4th step:

Now the search matrix has to be defined. We would like to search through all article numbers according to the criterion and select the entire range accordingly. Click on the upward pointing arrow next to Matrix again and mark the search area . This works by limiting the area by holding down the left mouse button. Then press [Enter] and the arguments will be adopted for the function.

5th step:

We would like to output the corresponding price of the article number within the search matrix. This is in the 2nd line. Therefore we enter "2" as the line index . If the values ​​to be searched for are in other lines, you must of course adjust this value.

6th step:

We enter " FALSE " under " Range_Reference " , since only exactly matching results should be displayed. Now click on " OK ".

7th step:

If necessary, adjust the formatting of the cell. Here we select " Currency ", for example . In order to understand what is practical about the HLOOKUP function, enter a different article number in cell A5 (or the corresponding cell of the search criterion). This means that the HLOOKUP cell is updated automatically and you no longer have to search for prices manually. This makes searching for large data tables much easier.

quick start Guide

1. Open the Excel document and define a search criterion in a cell.
2. Click the cell in which you want the result of the HLOOKUP search formula to appear.
3. Now go via " Formulas " to " Look up and reference " and finally to " HLOOKUP ".
4. Use the upward pointing arrows next to the parameters to define the search criterion, the matrix and the line index. This works simply by clicking on the cell for the criterion or by framing the area for the matrix.
5. Under " range_lookup " enter " FALSE an" if only just matching results to appear.
6. Now click on " OK ".
7. Change the cell with the search criterion as you like. The function cell updates itself automatically.

---