Question:     How can I search for a value in the top row of the table and then return a value in the same column from a specified row?

Answer:        By using Hlookup

Why:            To perform a horizontal lookup on a data list

Applies To:  Microsoft Excel 2003, 2007, 2010 

  1. Refer to the data given below;

2. To find the total sales for the South Coast;

  • Select any empty cell
  • Select as below
  •    Enter as below and select OK

The answer will be $40,000.00.

The formula can also be entered as  =HLOOKUP(C2,B2:E6,4,False)

The syntax of the Hlookup formula is as given below.

= HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Lookup_value: The value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string.

Table_array:  A table of information in which data is looked up. Use a reference to a range or a range name.

Row_index_num:  The row number in table_array from which the matching value will be returned.

Range_lookup:  A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned.. If FALSE, HLOOKUP will find an exact match.