HLOOKUP function in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the HLOOKUP function do in Excel?

HLOOKUP searches for a value in the first row of a table and returns a value in the same column from a row you specify.

beginner

What are the four arguments of the HLOOKUP function?

The four arguments are:<br>1. lookup_value: the value to find in the first row.<br>2. table_array: the range of cells to search.<br>3. row_index_num: the row number to return a value from.<br>4. range_lookup: TRUE for approximate match or FALSE for exact match.

beginner

How does setting the range_lookup argument to FALSE affect HLOOKUP?

Setting range_lookup to FALSE makes HLOOKUP look for an exact match only. If it doesn't find the exact value, it returns an error.

beginner

Example: =HLOOKUP("Jan", A1:D3, 2, FALSE)<br>What does this formula do?

It looks for "Jan" in the first row of the range A1:D3. If found, it returns the value from the second row in the same column as "Jan". It requires an exact match.

intermediate

What happens if HLOOKUP cannot find the lookup_value and range_lookup is TRUE?

HLOOKUP returns the closest smaller value found in the first row. The first row must be sorted in ascending order for this to work correctly.

What row does HLOOKUP search for the lookup_value?

A First column of the table_array

B First row of the table_array

C Last row of the table_array

D Last column of the table_array

HLOOKUP always searches the first row of the table_array for the lookup_value.

What does the row_index_num argument specify in HLOOKUP?

A The number of matches to find

B The column number to search

C The row number to return a value from

D The number of rows to skip

row_index_num tells HLOOKUP which row's value to return from the matched column.

If you want an exact match with HLOOKUP, what should range_lookup be set to?

A FALSE

B TRUE

C 0

D 1

Setting range_lookup to FALSE forces HLOOKUP to find an exact match.

What happens if HLOOKUP does not find an exact match and range_lookup is FALSE?

A Returns the closest smaller value

B Returns the first value in the row

C Returns zero

D Returns an error

With range_lookup FALSE, no exact match causes HLOOKUP to return an error.

For approximate match (range_lookup TRUE), what must be true about the first row?

A It must be sorted in ascending order

B It must be sorted in descending order

C It must contain only numbers

D It must have unique values

The first row must be sorted ascending for approximate match to work correctly.

Explain how the HLOOKUP function works and when you would use it.

Think about looking up a month name in a header row to find sales data below.

Describe the role of the range_lookup argument in HLOOKUP and how it affects the result.

Consider what happens if you want to find a price for a specific product code exactly or the closest available.