Excel VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP are lookup functions that search for a value in a table and return a related value from another column (VLOOKUP) or row (HLOOKUP). These are among the most widely used functions in Excel for connecting data across tables.

What is VLOOKUP?

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a table (vertically, top to bottom) and returns a value from a specified column in the same row.

The Analogy

Think of a printed phone directory. To find someone's number, a name is looked up in the leftmost column, and then the phone number from the same row is retrieved. VLOOKUP does the same thing electronically.

Syntax

  =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The value to search for (e.g., an employee ID, product code, or name).
• table_array: The range of cells that makes up the lookup table (must include the column being searched and the column to return).
• col_index_num: The column number in the table from which to return the result. Column 1 is the first column of the table.
• range_lookup: TRUE for approximate match, FALSE for exact match. Use FALSE for most cases.

VLOOKUP Example

Data Table (Columns A and B)

  | A         | B        |
1 | Emp ID    | Name     |
2 | 101       | Alice    |
3 | 102       | Bob      |
4 | 103       | Carol    |
5 | 104       | David    |

Lookup Formula

  Search for employee ID 103 and return the name.

  Formula: =VLOOKUP(103, A1:B5, 2, FALSE)

  Explanation:
  → 103 = lookup_value (what to search for)
  → A1:B5 = table_array (the data range)
  → 2 = col_index_num (return value from column 2, which is "Name")
  → FALSE = exact match

  Result: "Carol"

Extended Example with More Columns

  | A      | B         | C         | D         |
1 | ID     | Name      | Dept      | Salary    |
2 | 101    | Alice     | Sales     | 50000     |
3 | 102    | Bob       | IT        | 60000     |
4 | 103    | Carol     | HR        | 45000     |

  To find Department for ID 102:
  =VLOOKUP(102, A1:D4, 3, FALSE)
  Result: "IT"

  To find Salary for ID 103:
  =VLOOKUP(103, A1:D4, 4, FALSE)
  Result: 45000

Common VLOOKUP Errors

#N/A Error

The lookup value was not found in the first column of the table. This often happens due to typos or when the lookup value does not exist.

#REF! Error

The col_index_num is greater than the number of columns in the table array.

#VALUE! Error

An incorrect argument type was used — for example, col_index_num is text instead of a number.

Handling #N/A with IFERROR

  =IFERROR(VLOOKUP(E2, A1:D4, 2, FALSE), "Not Found")
  → Returns "Not Found" instead of #N/A if the ID doesn't exist.

Important VLOOKUP Limitations

• VLOOKUP can only search the first column of the table array. The lookup column must always be on the left.
• VLOOKUP returns only one result per lookup.
• VLOOKUP can only look to the right — it cannot return a value from a column to the left of the search column.

TRUE vs FALSE (Exact vs Approximate Match)

FALSE (Exact Match)

The lookup finds an exact match for the lookup value. If no exact match is found, an #N/A error is returned. This is the most commonly used setting.

TRUE (Approximate Match)

Used when the table is sorted in ascending order and the closest match (less than or equal to) is needed. This is useful for grade brackets or tax slabs.

Example with Approximate Match

  Grade table (sorted ascending):
  | A (Min Score) | B (Grade) |
  | 0             | F         |
  | 50            | D         |
  | 60            | C         |
  | 70            | B         |
  | 80            | A         |

  =VLOOKUP(75, A2:B6, 2, TRUE)
  → Finds 70 as the closest match ≤ 75
  Result: "B"

HLOOKUP Function

What It Does

HLOOKUP stands for Horizontal Lookup. It works exactly like VLOOKUP but searches across rows instead of down columns. It looks in the first row of the table and returns a value from a specified row below.

Syntax

  =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• row_index_num: The row number (within the table) from which to return the value. Row 1 is the header row.

Example

  | A     | B     | C    | D     |
1 | Month | Jan   | Feb  | Mar   |
2 | Sales | 4000  | 5500 | 4800  |
3 | Units | 200   | 260  | 230   |

  Find February's sales:
  =HLOOKUP("Feb", A1:D3, 2, FALSE)

  → Searches for "Feb" in row 1
  → Returns value from row 2 (Sales)
  Result: 5500

VLOOKUP vs HLOOKUP: When to Use Which

  VLOOKUP → Data is arranged in columns (most common)
             Search column is on the left
             Return values are to the right

  HLOOKUP → Data is arranged in rows (less common)
             Search row is at the top
             Return values are below

Summary

• VLOOKUP searches vertically (down the first column) and returns a value from a specified column in the same row.
• Use FALSE for exact match (most common) and TRUE for approximate match (sorted tables).
• VLOOKUP can only search the leftmost column of the table and return values to the right.
• HLOOKUP works the same way but searches horizontally across the first row of a table.
• Wrap VLOOKUP/HLOOKUP in IFERROR to handle #N/A errors gracefully.