Login

+3 votes

Best answer

** horizontal ** and refers to the first row of a table. The HLOOKUP function is particularly useful when working with very large tables, as it allows you to find the result much faster, rather than wasting time looking for values one by one. Here we use a concrete example to explain step by step how you can use the handy HLOOKUP function in Excel..

- What is HLOOKUP used for?
- Understand the syntax of HLOOKUP
- Practical example of Hlookup
- HLOOKUP: possible combinations with other functions

Many Excel users work with huge amounts of data on a day-to-day basis. In this case, ** maintaining the overview ** or ** quickly finding information ** not only takes time, but errors can occur. To find the right results in less time, Microsoft's program offers convenient search and reference functions. One of them is the HLOOKUP function, which allows you to search for values in a specific range of cells. This search begins in the first cell on the left and continues to the right until the end of the range defined in the function.

HLOOKUP has many uses: for example, imagine that you sell many different products and you want to record your sales in a table at the end of the month. It would be very cumbersome to search for each product in a price list to enter the respective price individually. With the Excel function HLOOKUP, you save this step. Only the products in the price list are required to be laid out ** horizontally. ** If this is the case, Excel enters the prices with the appropriate ** syntax ** automatically..

If the prices in your table are not horizontal, but ** vertical ** , use VLOOKUP. This function performs a top to bottom search within a given column.

Before you can use HLOOKUP, you must understand the syntax and enter it correctly. As with any Excel formula, the slightest deviation will generate an error message or an incorrect result. However, once you have understood the elements that compose it, it is very easy to apply and considerably simplifies the work. Written by hand, the ** structure ** of HLOOKUP looks like this:

` `` =BUSCARH(valor_buscado;matriz;indicador_filas;verdadero/falso) `

Each ** parameter ** has the following ** meaning ** :

- The
**equal sign**tells Excel that you are going to enter a formula in the edit bar and not a fixed value. - The
**searched value**consists of the word or value to be searched. If it is a term, it must be enclosed in quotation marks. - The
**array**contains the range of the table where the data is located. You can select it with the mouse or indicate manually. - The
**row indicator**indicates which row the result is in. For example, if the result is in the fifth row, the indicator is? 5 ?. - With
**true**or**false it**is indicated if the result is shown even when the match is not exact. If TRUE is chosen, approximate matches also appear, whereas with FALSE only exact matches appear.

In Excel, you don't have to type the HLOOKUP syntax manually in the edit bar. Use the ** formula generator ** to enter the most important data. The following example illustrates each step..

Excel HLOOKUP is particularly useful if you want to find a certain value in a large data record, even in documents with hundreds or thousands of entries. However, to ** explain the basic concept ** , a very simple example suffices. Below is a price list and an input mask:

Now, in cell B7, we want to know at what price the item with the number 102 is sold. To do this, select the cell that is going to show the result and click on ** Formulas ** -> ** Search and reference ** -> HLOOKUP. The Excel formula generator will appear automatically.

This is the window in which you can configure the formula. The ** searched value ** is item number 102, and the ** array ** covers the cell range ** B2 through E3 ** . Since the value you are looking for is in the second row of the array, the ** row indicator ** is ** 2 ** . In? Tidy? ** FALSE ** must be set to specify that we want an exact match.

After clicking on? OK ?, B7 will show the price of the corresponding item:

The HLOOKUP function in Excel can also be combined with other functions. In the following illustration, for example, we want to show the price for the respective quantity of items sold.

` `` Celda C7: =B7*BUSCARH(A7;B2:E3;2;FALSO) Celda C8: =B8*BUSCARH(A8;B2:E3;2;FALSO) `

To sum the two values in cell C10, for example, insert the SUM function with the array C7: C8.