HLOOKUP
Finds a value in the first row of a table and returns data from the same column: the plan or actual figure by month name.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])- [ ]optional part
- lookup_value
- What to look for: the cell with the key, for example a month in A2.
- table_array
- A lookup table with the keys in its first row.
- row_index_num
- Which row of the table to return the value from. The first row of the table is 1.
- range_lookup
- 0 means exact match. 1 means the nearest smaller value, only when the first row is sorted in ascending order.Optional
Return value
The value from the matching column, taken from the row with the given number. If the key isn't found, you get #N/A.
Examples
Actual sales by month
fx
A
B
C
D
E
F
G
H
1
2
3
4
5
Month
Actual, $
Month
January
February
March
February
Plan, $
400000
420000
450000
March
Actual, $
412000
387500
455300
Sheet1
Details
HLOOKUP works like VLOOKUP, but the table is turned sideways: the keys are in the first row, not the first column. The function finds the column with the key and returns the value from the row with the number you give.
The row number is counted from the start of the table, not from the start of the sheet. The last argument is almost always 0: that makes HLOOKUP look for an exact match, and if the key isn't there, it returns #N/A.
Common mistakes
- The keys are in a column, not a row. Use VLOOKUP for such tables.
- The row number is counted from the top of the sheet. Count from the first row of the table.
- Without $ signs the table shifts when you fill the formula, and the next cells get #N/A.