+5 votes
251 views
in Sell ​​on the Internet by (242k points)
reopened
What is the HLOOKUP function and how to use it step by step

1 Answer

+3 votes
by (1.6m points)
edited
 
Best answer

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

image

What is the HLOOKUP function and how to use it step by step

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..

Index
  1. What is HLOOKUP used for?
  2. Understand the syntax of HLOOKUP
  3. Practical example of Hlookup
  4. HLOOKUP: possible combinations with other functions

What is HLOOKUP used for?

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..

Note

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.

Understand the syntax of HLOOKUP

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..

Practical example of Hlookup

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:

image
In cell B7, the value for item # 102 should appear.

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.

image
HLOOKUP function in Excel formula builder.

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.

image
Indicates the parameters for HLOOKUP.

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

image
Example of HLOOKUP with result.

HLOOKUP: possible combinations with other functions

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.

 

image
You can adapt the HLOOKUP function to your needs.

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)  
image
HLOOKUP: cells C7 and C8 with results.

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


...