### HLOOKUP Function

The __HLOOKUP__ function returns the value in a row that matches a value in the top row of a table__XLOOKUP__ was added in __Microsoft 365__ to replace this function.

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, sorted or not sorted)

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)

You can get the last occurrence when the "lookup_value" row is not sorted by using the __LOOKUP__ function.

The LOOKUP function uses an array of cell values and its closest match logic to find the last match.

The array eventually contains either 1's or error values and because the "lookup_value" is 2 it will return the position of the last 1 in the array.

You could use the __INDEX__ and __MATCH__ functions with an array formula as an alternative.

You could use the __INDEX__, __MAX__ and __COLUMN__ with an array formula as another alternative.

You could combine with the __SUMPRODUCT__ function to remove the array formula.

#### Return the Exact Match (with wildcard *)

An asterisk (*) matches any sequence of characters.

The "range_lookup" argument must be FALSE.

You can have an asterisk as the first character.

#### Return the Exact Match (with wildcard ?)

A question mark (?) matches any single character.

The "range_lookup" argument must be FALSE.

You can have a question mark as the first character.

#### Return the Exact Match (multiple criteria)

When you want to return a value in the same column after finding matching values in 2 (or more) rows.

There are lots of different ways you can __Lookup Multiple Criteria__.

#### 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.

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