| | | This function should be used when you want to lookup a matching value in a particular row of a table and then return a value in the corresponding column. |
| | | This function cannot return values that are above the lookup row. |
| | | This function is not case sensitive when searching for text strings. Uppercase and lowercase text is equivalent. |
| | | The "lookup_value" can be a number, text, logical value, or a name or reference that refers to a one of these. |
| | | If "lookup_value" is text, then it can also include the two wildcard characters ( * ) and ( ? ). |
| | | If "lookup_value" is smaller than the smallest value in the first row of "table_array", then #N/A is returned. |
| | | If "lookup_value" cannot be found and "range_lookup" is TRUE, it uses the largest value that is less than the "lookup_value". |
| | | If "table_array" contains duplicate values then the results are based on the first match found. |
| | | If "row_index_num" = 1, the first row value in "table_array" is used. |
| | | If "row_index_num" = 2, the second row value in "table_array", is used. |
| | | If "row_index_num" < 1, then #VALUE! is returned. |
| | | If "row_index_num" > the number of rows in "table_array", then #REF! is returned. |
| | | If "row_index_num" is not numeric, then #VALUE! is returned. |
| | | If "range_lookup" = True then an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than "lookup_value" is returned. |
| | | If "range_lookup" = True, then the values must be sorted into ascending order -1, 0, 1, A-Z, FALSE, TRUE, otherwise the function will not return the next largest value. |
| | | If "range_lookup" = False, then an exact match will be found. If one is not found, then #N/A is returned. |
| | | If "range_lookup" = False, "table_array" does not need to be sorted. |
| | | If "range_lookup" is left blank, then True is used. |
| | | This function will only return values less than 256 characters long. If the matching value is more than 255 characters, then #N/A is returned. |
| | | For a working example please refer to the HLOOKUP Function page. |
| | | | A | B | C | D | | 1 | =HLOOKUP("Wheels",B1:D2,2,TRUE) = 11 | Wheels | Bearings | Bolts | | 2 | =HLOOKUP("Wheels",B1:D2,1,TRUE) = Bolts | 6 | 8 | 11 | | 3 | =HLOOKUP("Wheels",B1:D2,1) = Bolts | | | | | 4 | =HLOOKUP("Wheels",B1:D3,2) = 11 | | | | | 5 | =HLOOKUP("Wheels",B1:D3,1,FALSE) = Wheels | | | | | 6 | =HLOOKUP("Wheels",B2:D4,2,FALSE) = #N/A | | | |
| |
