HLOOKUP for quarterly targets
BeginnerVLOOKUP searches down a column to find a match. HLOOKUP does the same thing, but searches across a row instead. It's the right tool when your reference data is laid out in rows rather than columns.
HLOOKUP syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: the quarter name to search for
- table_array: the range that contains both the header row and the values row
- row_index_num: which row within the table to return a value from (1 = first row, 2 = second row)
- range_lookup: use FALSE for an exact match
In this sheet, the targets table is in A1:E2. Row 1 has a row-label column in A1 and the quarter headers Q1 through Q4 in columns B through E. Row 2 contains the corresponding sales targets. The quarter you want to look up is in B4.
Your task
- Click cell B5.
- Write an HLOOKUP formula that looks up the quarter in B4 within the targets table in A1:E2.
- Return the sales target from row 2 of the table.
- Use FALSE for an exact match.
When you're done, cell B5 should show the sales target for the quarter in B4.
Need some help?
Hint 1
The lookup_value argument should be a cell reference, not a typed value. Point HLOOKUP at the cell that contains the quarter name rather than typing the quarter name directly into the formula.
Hint 2
For this exercise, your four arguments are: the cell that holds the quarter name, the five-column range that contains both the header row and the target row, the row number where the dollar figures live, and the flag for an exact match.
HLOOKUP for quarterly targets
BeginnerVLOOKUP searches down a column to find a match. HLOOKUP does the same thing, but searches across a row instead. It's the right tool when your reference data is laid out in rows rather than columns.
HLOOKUP syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: the quarter name to search for
- table_array: the range that contains both the header row and the values row
- row_index_num: which row within the table to return a value from (1 = first row, 2 = second row)
- range_lookup: use FALSE for an exact match
In this sheet, the targets table is in A1:E2. Row 1 has a row-label column in A1 and the quarter headers Q1 through Q4 in columns B through E. Row 2 contains the corresponding sales targets. The quarter you want to look up is in B4.
Your task
- Click cell B5.
- Write an HLOOKUP formula that looks up the quarter in B4 within the targets table in A1:E2.
- Return the sales target from row 2 of the table.
- Use FALSE for an exact match.
When you're done, cell B5 should show the sales target for the quarter in B4.
Need some help?
Hint 1
The lookup_value argument should be a cell reference, not a typed value. Point HLOOKUP at the cell that contains the quarter name rather than typing the quarter name directly into the formula.
Hint 2
For this exercise, your four arguments are: the cell that holds the quarter name, the five-column range that contains both the header row and the target row, the row number where the dollar figures live, and the flag for an exact match.