The HLOOKUP function returns a value that is in the same column as the value you search for.
Formula:
= HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Explanation:
Lookup_value is required, the value to look for.
Table_array is required, the data range or table where you need to search from.
- If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order.
- If range_lookup is FALSE, table_array does not need to be sorted.
Row_index_num is required, the row number from which the matching value returned.
Range_lookup is optional. A logical value that specifies an exact match or an approximate match.
- If TRUE or omitted, an approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
- If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.
=HLOOKUP("JACKSON",$A$1:$D$6,2)
Approximate match (omitted): Looks up "JACKSON " in row 1, and returns the value from row 2 that's in the same column. Because an exact match for "JACKSON" is not found, the largest value in row 1 that is less than "JACKSON" is used: "First Name" in column A. The value in column A and 2nd row is "Jackson".
=HLOOKUP("Jackson",$A$1:$C$6,3,TRUE)
Approximate match (true): Looks up "Jackson " in row 1, and returns the value from row 3 in the same column. Because an exact match for "Jackson" is not found, the largest value in row 1 that is less than "Jackson" is used: "First Name" in column A. The value in column A and 3rd row is "Aiden".
=HLOOKUP("JACKSON",$A$1:$C$6,4,FALSE)
Exact match (false): Looks up "JACKSON " in row 1, and returns the value from row 4 that's in the same column. Because an exact match for "JACKSON" is not found, it returns "#N/A" error.