Hlookup Ref Error
Contents |
Hlookup function 'looks up' a given value in the top row of a data array (or table), and returns the corresponding value from another row of the array.The syntax of the function is:HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )Where the function arguments are as follows:lookup_value-The value that you want to search for, http://www.excelfunctions.net/Excel-Hlookup-Function.html in the first row of the supplied data array.table_array-The data array or table, containing http://spreadsheets.about.com/od/excelfunctions/ss/hlookup.htm the data to be searched in the top row, and the return values in any other row.row_index_num-The row number, within the supplied table_array, that you want the corresponding value to be returned from.[range_lookup]-An optional logical argument, which can be set to TRUE or FALSE, meaning:TRUE-if the function cannot find an exact match to the supplied lookup_value, hlookup in it should use the closest match below the supplied value.Note: If [range_lookup] is set to TRUE, the top row of the table_array must be in ascending order).FALSE-if the function cannot find an exact match to the supplied lookup_value, it should return an error.WildcardsIn text-related Hlookups, when the [match_type] argument is set to 0, the lookup_value can contain the following wildcard characters:? - matches any single character* - hlookup in excel matches any sequence of charactersHlookup Function ExamplesHlookup Example 1 - Exact Match LookupCells A2-F6 of the spreadsheet below, show the exam scores for 5 students in 4 different subjects. If you want to look up a specific score (e.g. Biology) for one of the students (e.g. Ed), this can be done using the Hlookup function, as shown in cell B10 of the spreadsheet.Formulas:Results:In the above example, the Hlookup function searches through the top row of the table_array (the range A2-F2), to find a match for the lookup_value (the name "Ed"). When the the name "Ed" is found, the function returns the corresponding value from the 5th row of the table_array.This is illustrated in the above spreadsheet on the right. The function finds the name 'Ed' in the top row of the table_array and then returns the value from the 5th row of the table_array.If we change the name in cell A10 of the spreadsheet from 'Ed' to 'Cara', the Hlookup functions would automatically recalculate the function to display the exam results for Cara.Hlookup Example 2 - Closest Match LookupCells A1-F3 of the spreadsheet below, show body types relating to body mass index (BMI), for the ranges 0-18.4, 18.5-24.9, 25.0-29.9 and over 30.Cell C6 shows the user's current BMI, whic