• Lookup function returns a value from a range (one row or one column) or from an array.
  • The Lookup function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. 
  • As a worksheet function, the Lookup function can be entered as part of a formula in a cell of a worksheet.
     Syntax:
  • LOOKUP( value, lookup_range, [ result_range ])
     Function arguments:
  • value: 
    The value to search for in the lookup_range.
  • lookup_range: A single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range.
  • result_range: It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, it will return the first column of data.
  • The LOOKUP function returns any datatype such as a string, numeric, date, etc.
      Example:
  • Results for above example
  • Two types of Lookup functions used in Excel:
     VLOOKUP:
  • Lookup formula can work vertically in Vlookup.
  • When you need to find things in a table or a range by row.
     Syntax:
  • VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
     Vlookup function arguments:
  • lookup_value: The value to look for in the first column of table.
  • table_array: The table from which to retrieve a value.
  • col_index_num: The column in the table from which to retrieve a value.
  • range_lookup:  
  • True = approximate match (default).
  • False = exact match.
     Example:
  •  VLOOKUP (F1, A1:B11, 2, FALSE).
   
  • If the item not in lookup table they default gives "#N/A", if you want change #N/A to another word "No" then we can use "IFERROR".


Image result for vlookup example
     HLOOKUP:
  • Lookup formula can work horizontally in Hlookup.
  • It searches for a certain value in the first row of the table and returns another value in the same column from a row that you specify.
     Syntax:
  • HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]) 
     Hlookup function arguments:
  • lookup_value: The value to search for.  It can be a cell reference, numeric value or text string.
  • Table_array: Two or more rows of data in which the lookup value is searched.  Lookup values should always be located in the first row of table_array.
  • Row_index_num: The row number in table_array from which the value should be returned.
  • Range_lookup: A logical  value that instructs HLOOKUP to search with exact or approximate match.
  • True = approximate match (default).
  • False = exact match.
  • If no value in a specified row matches the lookup value exactly, a Hlookup formula returns the #N/A error.
  • The HLOOKUP function can only search in the top-most row of table_array.
  • HLOOKUP in Excel is case-insensitive, it does not distinguish uppercase and lowercase.
     Example:
  • Find the atomic mass of Boron.
Image result for hlookup example
  • Finding marks of students in English.
Image result for hlookup example