hlookup

 

The HLOOKUP function in Excel is used to search for a value in the top row of a table or range and return a value in the same column from a specified row. Below are 10 examples of how to use HLOOKUP, including sample data and formulas.

---

Example 1: Basic HLOOKUP

Data:

Copy

A1: Apple   B1: Banana   C1: Cherry
A2: 10      B2: 20       C2: 30

Formula:

excel

Copy

=HLOOKUP("Banana", A1:C2, 2, FALSE)

Result: 20
Explanation: Searches for "Banana" in the first row and returns the value from the second row in the same column.

---

Example 2: Approximate Match

Data:

Copy

A1: 10      B1: 20       C1: 30
A2: Low     B2: Medium   C2: High

Formula:

excel

Copy

=HLOOKUP(25, A1:C2, 2, TRUE)

Result: Medium
Explanation: Searches for 25 in the first row. Since 25 is between 20 and 30, it returns "Medium" (approximate match).

---

Example 3: HLOOKUP with Wildcard

Data:

Copy

A1: Apple   B1: Banana   C1: Cherry
A2: 10      B2: 20       C2: 30

Formula:

excel

Copy

=HLOOKUP("B*", A1:C2, 2, FALSE)

Result: 20
Explanation: Uses the wildcard * to match "Banana" and returns the corresponding value from the second row.

---

Example 4: HLOOKUP with Multiple Rows

Data:

Copy

A1: Apple   B1: Banana   C1: Cherry
A2: 10      B2: 20       C2: 30
A3: Red     B3: Yellow   C3: Red

Formula:

excel

Copy

=HLOOKUP("Banana", A1:C3, 3, FALSE)

Result: Yellow
Explanation: Searches for "Banana" in the first row and returns the value from the third row in the same column.

---

Example 5: HLOOKUP with Dynamic Row Index

Data:

Copy

A1: Apple   B1: Banana   C1: Cherry
A2: 10      B2: 20       C2: 30
A3: Red     B3: Yellow   C3: Red

Formula:

excel

Copy

=HLOOKUP("Banana", A1:C3, ROW(A2), FALSE)

Result: 20
Explanation: Uses the ROW function to dynamically specify the row index (2 in this case).

---

Example 6: HLOOKUP with IFERROR

Data:

Copy

A1: Apple   B1: Banana   C1: Cherry
A2: 10      B2: 20       C2: 30

Formula:

excel

Copy

=IFERROR(HLOOKUP("Grape", A1:C2, 2, FALSE), "Not Found")

Result: Not Found
Explanation: If "Grape" is not found, the IFERROR function returns "Not Found".

---

Example 7: HLOOKUP with MATCH for Dynamic Column

Data:

Copy

A1: Apple   B1: Banana   C1: Cherry
A2: 10      B2: 20       C2: 30

Formula:

excel

Copy

=HLOOKUP("Banana", A1:C2, MATCH("Price", A1:A2, 0), FALSE)

Result: 20
Explanation: Uses MATCH to dynamically find the row index for "Price" (assumed to be in column A).

---

Example 8: HLOOKUP with Array Formula

Data:

Copy

A1: Apple   B1: Banana   C1: Cherry
A2: 10      B2: 20       C2: 30

Formula:

excel

Copy

=HLOOKUP({"Apple", "Banana"}, A1:C2, 2, FALSE)

Result: {10, 20}
Explanation: Returns an array of values for "Apple" and "Banana".

---

Example 9: HLOOKUP with Named Range

Data:

Copy

A1: Apple   B1: Banana   C1: Cherry
A2: 10      B2: 20       C2: 30

Steps:

1. Select A1:C2 and name the range FruitData.

2. Use the formula:

   excel

   Copy

   =HLOOKUP("Cherry", FruitData, 2, FALSE)

Result: 30
Explanation: Uses a named range FruitData for the lookup.

---

Example 10: HLOOKUP with INDIRECT for Dynamic Range

Data:

Copy

A1: Apple   B1: Banana   C1: Cherry
A2: 10      B2: 20       C2: 30

Formula:

excel

Copy

=HLOOKUP("Banana", INDIRECT("A1:C2"), 2, FALSE)

Result: 20
Explanation: Uses INDIRECT to dynamically reference the range A1:C2.

---

These examples demonstrate the versatility of the HLOOKUP function in Excel. You can adapt these formulas to suit your specific data and requirements.