Lookup values by column in a horizontal table or range

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

---

Try using the new XLOOKUP function instead of HLOOKUP. XLOOKUP works in any direction (not just down) and returns exact matches by default (not approximate matches as with HLOOKUP).

---

The H in HLOOKUP stands for horizontal. In a horizontally organized table or range, return a value in the same column but a different row to the lookup_value using either exact or approximate matching.

HLOOKUP defines 'what to lookup', 'where to lookup', 'the row number to return' and 'whether to return an approximate or exact match'.

For example, =HLOOKUP("Bang*",C2:G12,10,0) will look for wildcard values that begin with "Bang" (e.g. Bangladesh) in row 2 (first row) and will then return the matching result from 10 rows below (row 11) in the range C2:G12. The 0 (FALSE) indicates an exact match is required. #N/A is returned if there is no match. Use the IFNA function to trap and handle #N/A errors.

Use VLOOKUP to return a value in the same row but a different column to the lookup_value.

---

HLOOKUP and exact searches.

In the examples below, the HLOOKUP function is used to match population data by year to specific countries and perform calculations on population data.

---

HLOOKUP and approximate searches.

In the examples below, the HLOOKUP function is used to match population data to specific ranges of data values.

---

Syntax

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

The HLOOKUP function consists of at least 3 arguments:

1. lookup_value. Required. The value to be looked for in the first row of a table or range. Can be a value, reference or text string.
2. table_array. Required. The table or range across which to lookup the end result.
3. row_index_num. Required. The row in the table or range from which to retrieve the end result.
4. range_lookup. Optional. TRUE or 1 for an approximate lookup. FALSE or 0 for an exact lookup. If range_lookup is omitted, the HLOOKUP function will default to the closest approximate match. If range_lookup is TRUE (default and approximate), the lookup values in the first row of the table or range need to be in ascending order. If range_lookup is FALSE (exact), the lookup values in the first row do not to be sorted.

---

Try it now!

• Enter your formulas into the grey cells.

---

Scope

HLOOKUP defines 'what to lookup', 'where to lookup', 'the row number to return' and 'whether to return an approximate or exact match'.

Exact:	=HLOOKUP("United States",C2:G12,10,FALSE) || result is a value from the 10th row in the data range C2:G12 which is an exact match to the lookup value "United States".
Approximate:	=HLOOKUP(E6,mn_growth,2,TRUE) || result is a value from the 2nd row in the named range "mn_growth" which is an approximate match to the lookup value in E6 (in this case a number).
Combination:	&=CONCAT(HLOOKUP("Bang*",C2:G12,2,FALSE),", ",HLOOKUP(F8,mn_growth,2,1)) || result is a concatenation of an exact match for a wildcard value from the first HLOOKUP and an approximate match for a number in the second HLOOKUP.

Caveats

• HLOOKUP only looks down in a horizontally organized table or range.
  • Use VLOOKUP for vertical data or INDEX and MATCH, OFFSET or XLOOKUP for working in other directions.
• lookup_value
  • Data sets need to be organized so that the lookup_value appears in the first row of a horizontal table or range. The values to be returned need to be in rows beneath the lookup_value.
  • Create a helper column in the first row of the table or range to join multiple fields (e.g. firstname AND lastname) together to enable the lookup_value to handle multiple criteria.
  • HLOOKUP supports wildcards to perform partial matches on a lookup_value. The range_lookup argument must be exact (FALSE, 0) when using wildcards. The asterisk * matches any sequence of characters. The question mark ? matches any single character. To find an actual question mark or asterisk, type a tilde (~) in front of the character.
  • Don't store numbers or date values as text, otherwise HLOOKUP might return erroneous values.
  • Ensure data in the first row doesn't have leading spaces, trailing spaces, inconsistent use of straight (' or ") and curly (‘ or “) quotation marks, or nonprinting characters, otherwise HLOOKUP might return erroneous values. Use the CLEAN or TRIM function to clean up data.
• table_array
  • Use absolute references for table_array to limit errors when copying or filling the formula.
  • Sort the first row of the table_array when using a HLOOKUP for approximate matches (range_lookup is TRUE or 1).
• row_index_num
  • a row_index_num greater than the number of rows in table_array will return a #REF! error
  • Dynamic row_index_num values are possible using the MATCH function.
• range_lookup
  • The range_lookup argument in HLOOKUP defaults to TRUE or 1 which uses approximate matching. This can lead to erroneous results, so use carefully or use FALSE or 0 to ensure exact matches are returned.
  • Approximate matching (TRUE, 1) is useful when placing numbers within a range, e.g. placing the value 2.3% into a range 0-5%. This prevents the need to having to lookup every iteration of a number. Data must be sorted in ascending order when using the approximate lookup argument. If range_lookup is TRUE (or omitted), and no exact match is found, HLOOKUP will match the nearest value in the table that is still less than the lookup value.
  • Exact matches (FALSE, 0) will return the first match if there are duplicate values.
  • #N/A is returned if there is no match. Use the IFNA function to trap and handle #N/A errors. The #N/A error is an indication of missing or misspelt values, incorrect ranges or that the range_lookup is set to exact instead of approximate.

---

Common issues

• Incorrect value returned: If range_lookup is TRUE or omitted, the first row needs to be sorted alphabetically or numerically, otherwise the return value might be incorrect. Either sort the first row, or use FALSE for an exact match.
• #N/A: If range_lookup is TRUE or omitted, the lookup_value is smaller than the smallest value in the table_array.
• #N/A: If range_lookup is FALSE, an exact match can not be made.
• #REF!: row_index_num is likely greater than the number of rows in table_array.
• #VALUE!: table_array is less than 1.
• #NAME?: text values might be missing quotations.
• #SPILL!: lookup_value is likely set as an entire row (1:1) rather than a cell reference (C2).

---

Related Functions

• VLOOKUP to lookup values by row in a vertical table or range.
• INDEX and MATCH to lookup values in any direction.
• OFFSET to lookup values in any direction.
• XLOOKUP to lookup values in any direction.

---

Functions Category

• Lookup & Reference

 

---