Excel's HLOOKUP function makes it easy to find data in a predefined table. The H? of HLOOKUP means 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..
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 :
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.
Use the following formulas to multiply the contents of cells B7 and B8 by the corresponding values in the price list and display them in C7 and C8:
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.