HLOOKUP is suitable for horizontal data search, and its syntax is =HLOOKUP (find value, search range, line number, [whether it is approximately matched]). When using it, make sure that the search value is on the first row of the search range. It is recommended to use absolute reference and note that the line number does not exceed the total number of rows in the search range. Alternative solutions include INDEX MATCH, VLOOKUP after transpose, or VLOOKUP for long tables.

HLOOKUP in Excel is a very practical function, especially when you need to look up data in row directions in tables. It is similar to VLOOKUP, except that one is horizontal search (H means Horizontal) and the other is vertical search (V means Vertical). If your data is organized by row, HLOOKUP can come in handy.

When should I use HLOOKUP?

HLOOKUP is best suited for situations where data is arranged in "rows". for example:

• The header is a series of dates, and you need to find the corresponding value based on a certain date;
• Each row represents an indicator and the column represents a different month or region;
• Data structures are in the form of wide tables, not long tables.

Simply put, when you need to find a value from the first row and return the data at the corresponding position of the row below, you can consider using HLOOKUP.

Basic syntax and parameter description of HLOOKUP

The formula structure of HLOOKUP is as follows:

 =HLOOKUP (find value, search range, line number, [whether approximate match])

Meaning of each parameter:

• Find value : what you are looking for, such as a name, date, or number;
• Find range : The area containing data must be ensured that the first row has the value you are looking for;
• Line number : The number starts from the first row of the search range, and the data of the first row to be returned (note that it is not the row number of the worksheet);
• Whether it is approximate match : Usually fill in FALSE (exact match) or TRUE (approximate match), it is generally recommended to use FALSE .

For example:

 =HLOOKUP("Feb", A1:G5, 3, FALSE)

The meaning of this formula is: look for "Feb" in the first row of the region A1 to G5, and then return the value of the corresponding column of the third row after finding it.

Several things to note when using HLOOKUP

Although HLOOKUP is very convenient, there are several details that are particularly prone to errors and need to be noted:

• The search value must appear on the first line of the search range
  If the search range you specify starts from the second line, then the first line does not have the value you want to check, and an error will be reported.

• It is best to set the search range to absolute reference
  For example, write $A$1:$G$5 so that there will be no errors when dragging the formula.

• The row number cannot exceed the total number of rows in the search range
  For example, if the search range is only 3 lines, but you write the line number 4, Excel will return an error.

• #N/A error will be returned when the search value does not exist
  It can be used in conjunction with the IFERROR function to make the result more friendly, for example:

   =IFERROR(HLOOKUP("Feb", A1:G5, 3, FALSE), "Not Found")

Alternative: What to do if you don't want to use HLOOKUP?

Sometimes you will find that HLOOKUP is not very flexible, for example, it does not support upward search (returning the data on the first line). At this time, you can consider the following alternative:

• INDEX MATCH combination : more flexible, supports any direction search
• TRANSPOSE Use VLOOKUP after transposing data : suitable for one-time analysis
• Reconstructing data format : converting wide table into long table, using VLOOKUP is more convenient

For example, use INDEX and MATCH to implement HLOOKUP-like functions:

 =INDEX(A2:G5, 2, MATCH("Feb", A1:G1, 0))

The advantage of this combination is that it does not require relying on the first behavior to find rows, and it is not prone to errors.

Basically that's it. Although HLOOKUP is not as commonly used as VLOOKUP, it is quite useful in specific scenarios. As long as you pay attention to the problem of finding range and line number, you can avoid many common mistakes.

The above is the detailed content of how to use hlookup in excel. For more information, please follow other related articles on the PHP Chinese website!