Рет қаралды 88
Lookup functions are extremely useful as it allows us to search for a value in one column or row and return a value on the same row or column as the found value. Two really common functions in this category is VLOOKUP or Vertical Lookup and HLOOKUP or Horizontal Lookup.
To use VLOOKUP, simply write equals VLOOKUP and in the paranthesis, define “what you are searching for”, the “area you want to search and return”, and the column number in which the value you wish to return exists. As the name suggest, VLOOKUP searches for a value in the FIRST COLUMN of the defined area and returns a value in the SAME ROW. The value returned is determined by the index, where 1 returns the search value, 2 returns the value 1 step to the right of the search value and so on…
To use HLOOKUP, simply write equals HLOOKUP and in the paranthesis, define “what you are searching for”, the “area you wish to search and return values from” and the row number from which you wish to return a value. Again, the value returned is determined by the index, where 1 returns the search value, 2 returns the value 1 step down of the search value and so on…
Finally, there is XLOOKUP, which you may consider the newer and improved version of VLOOKUP and HLOOKUP. XLOOKUP is more flexible, easier to use and requires less processing power compared to V- and HLOOKUP. To use XLOOKUP, simply write equals XLOOKUP and in the paranthesis define “what you are searching for”, the row or column you wish to search and the row or column you wish to return. Another great thing with XLOOKUP is that it allows us to return values to the left or up from the search value and not just to the right and down as the case is with V- and HLOOKUP.
With all 3 of these LOOKUP functions, it is important to include a FALSE statement or 0 as the final argument, in order to ensure that a value is returned only when reaching an EXACT MATCH!