8+ Excel Hlookup 使い方 References

What is HLOOKUP in Excel // Excel glossary // PerfectXL from www.perfectxl.com

Introduction

Excel is a powerful tool that is widely used in businesses and personal life. HLOOKUP is one of the most useful functions in Excel that allows you to search for a value in the top row of a table and return a corresponding value in the same column from a row you specify. In this article, we will discuss the various ways to use Excel HLOOKUP in 2023.

Understanding HLOOKUP

HLOOKUP stands for "Horizontal Lookup." It is used to search for a value in the top row of a table and then return a corresponding value from a row that you specify. HLOOKUP is useful when you have a table with several rows and want to find a specific value in a particular column.

Syntax of HLOOKUP

The syntax of the HLOOKUP function is as follows: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

lookup_value: The value you want to search for in the top row of the table.
table_array: The table you want to search in.
row_index_num: The row number from which you want to return a value.
range_lookup: An optional parameter that specifies whether you want an exact match or an approximate match. If this parameter is omitted, it defaults to TRUE, which means that an approximate match is used.

Using HLOOKUP to Find Data in a Table

Let's say you have a table with sales data for different products and regions. You want to find the sales figures for a particular product in a specific region. Here's how you can use HLOOKUP to do this:

1. Type the name of the product you want to find in a cell.

2. Select the cell where you want to display the sales figure.

3. Type the HLOOKUP formula in the formula bar.

4. Press Enter.

Example of HLOOKUP

Let's say you have a table like the one below:

Product Name	Region 1	Region 2	Region 3
Product A	100	200	300
Product B	150	250	350
Product C	200	300	400

If you want to find the sales figures for Product B in Region 2, you would use the following HLOOKUP formula:

=HLOOKUP("Product B", A1:D4, 3, FALSE)

This formula tells Excel to search for "Product B" in the top row of the table A1:D4 and return the value from row 3 (which corresponds to Region 2).

Using HLOOKUP with IFERROR

If you use HLOOKUP to search for a value that does not exist in the top row of the table, you will get an error. To avoid this error, you can use the IFERROR function. Here's how you can use HLOOKUP with IFERROR:

=IFERROR(HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]), "Not Found")

This formula will return "Not Found" if the value you are searching for is not found in the top row of the table.

Conclusion

HLOOKUP is a powerful function in Excel that can help you quickly find data in a table. Whether you are working with sales data, inventory data, or any other type of data, HLOOKUP can help you find the information you need. By using the tips and tricks outlined in this article, you can become an Excel HLOOKUP expert in 2023.