HLOOKUP is a function in Excel for horizontal search and is suitable for finding data by row. Its syntax is =HLOOKUP (find value, table area, row number, [whether approximate match]). The search value must be located in the first row of the table area, and the row number cannot exceed the table range. It is recommended to use FALSE to ensure accurate match. For example =HLOOKUP("March", A1:D5, 3, FALSE) means to look for "March" in the A1 to D5 area and return the data in row 3 of the column. Pay attention to data structure and parameter settings when using it, and combining the MATCH function can improve flexibility.

HLOOKUP is a very practical search function in Excel, especially suitable for horizontal search data. If you process table data frequently, mastering this function can save a lot of time.

What is HLOOKUP?

The full name of HLOOKUP is "Horizontal Lookup", which means horizontal search. It is used to find data by row in Excel tables, usually used to find values in a specific row that match the specified criteria.

For example: If you have a sales data sheet, the first row is the month, and the following rows are the sales of different products, you can use HLOOKUP to find the sales of a product in a specific month.

Basic syntax of HLOOKUP function

The basic writing method of HLOOKUP is:

 =HLOOKUP (find value, table area, line number, [whether approximate match])

• Find value : What you are looking for, such as a month or product name.
• Table area : The area you are looking for is usually a range that contains multiple rows and columns.
• Row number : In the table area, what you want to return is the data of the first row of the table area (the number starts from the first row of the table area).
• Whether it is approximate match : It is generally written as FALSE (exact match) or TRUE (approximate match), and in most cases FALSE is used.

For example:

 =HLOOKUP("March", A1:D5, 3, FALSE)

This formula means looking for "March" in the areas A1 to D5 and returning the data for row 3 in that column.

Several things to note when using HLOOKUP

The search value must be on the first row of the table area

HLOOKUP is a row-by-row search, which can only find matches in the first row of the specified table area. If the search value is not on the first line, the function returns an error.

The table area should be sorted by the search value (if TRUE is used)

If you set to TRUE , that is, approximate matches, the first row of the table area must be arranged in ascending order, otherwise the result may be inaccurate.

The line number cannot exceed the table area

For example, if your table area has only 3 rows, but if you write it as 4 , the function will return an error.

Try to use FALSE to ensure accurate matching

Unless you do need an approximate match, it is recommended to use FALSE , which is more reliable.

Practical application examples

Suppose you have a table with the following structure:

If you want to find the sales data of "Product B" in "February", you can write it like this:

 =HLOOKUP("February", A1:D3, 2, FALSE)

here:

• The search value is "February"
• The table area is A1 to D3 (the first row is the title)
• What to return is line 2 (that is, the data of product B)

The result is 110 .

Tips: Combining MATCH to improve flexibility

If you do not want to specify the line number manually, you can combine MATCH function to dynamically obtain the line number. For example:

 =HLOOKUP("March", A1:D3, MATCH("Product A", A1:A3, 0), FALSE)

In this way, you can first find which row "Product A" is in, and then combine it with HLOOKUP to find the data for the corresponding month, which is more flexible.

Basically that's it. HLOOKUP is not complicated, but it is easy to cause errors due to errors in the region selection or wrong line number. As long as you pay attention to the table structure and parameter order, it is still easy to use.

The above is the detailed content of How to use the HLOOKUP function in Excel. For more information, please follow other related articles on the PHP Chinese website!