HLOOKUP Function

The HLOOKUP function returns the value in a row that matches a value in the top row of a table
This looks up a value in the first row of a table and returns the value in the same column but in a different row.
This function is not case sensitive.


Return the Exact Match (unique values)

When the "range_lookup" argument is FALSE an exact match is found.
When looking for an exact match, the table does not have to be sorted.


Return the Exact Match (item does not exist)

When a value is not in the list, then #N/A is returned.
When the "range_lookup" argument is FALSE an exact match is found.
The table does not have to be sorted when looking for an exact match.


Return the Exact Match (case sensitive)

A case sensitive lookup can be achieved using the INDEX and MATCH combination.
The HLOOKUP function is not case sensitive.
This formula must be entered as an Array Formula using (Ctrl + Shift + Enter).
The "0" passed to the MATCH function means that it will find the first value that matches the lookup value exactly.


Return the Exact Match (first occurrence)

This function will always return the first occurrence, starting on the left.
When the "range_lookup" argument is FALSE an exact match is found.
The table does not have to be sorted to return the first occurrence.


Return the Exact Match (last occurrence, sorted)

The last occurrence can be achieved when the lookup row is sorted into ascending order.
The table has been sorted by Country into ascending order.


Return the Exact Match (last occurrence, not sorted)



Return the Exact Match (multiple criteria)

A multiple criteria lookup can be achieved using the INDEX and MATCH combination.
When you want to return a value in the same column after finding matching values in 2 (or more) rows.


Return the Next Smallest

The next smallest value can achieved when the lookup column is sorted into ascending order.
When the "range_lookup" argument is TRUE the nearest smallest value is found.
When the "range_lookup" argument is TRUE the table must be sorted.
The table has been sorted by Units into ascending order.


Return the Next Largest

The next largest value can be achieved using the INDEX and MATCH combination.
The table has been sorted by Units into descending order.
The "-1" passed to the MATCH function means that it will find the smallest value that is greater than the lookup value.


Combining with MATCH

A two way lookup can be achieved using a HLOOKUP and MATCH combination.
A two way lookup is when you return the intersection of a matching column with a matching row.


Important

When no match is found and you want to return approximate values, the first row of your table must be sorted in ascending order.
If you are searching for numbers then they must be formatted as numbers and not as text. If they are formatted as text they will be ignored.
This function can only be used to return values in rows below the lookup column.
The lookup row has to be the first row in the table.


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext