Sheets Formula- VLookup and HLookup
These sheets formulae are very user-friendly and time-saving functions. The user can easily use these functions to locate, manipulate and analyze their data in the spreadsheet.
Google Sheets is one of the most reckoned and user-friendly software and because of this feature, sheets are being widely used all over the world. It is very user-friendly and understands the data analysis and manipulations very easily.
The Lookup functions are used to search the values in column and row. Two specific lookup functions are used- VLookup and HLookup. The Letter ‘V’ stands for Vertical and the letter ‘H’ stands for Horizontal value. The hands-on experience on these formulas is mandatory in some jobs. At the initial stage using these formulae will be difficult for the user but once they get used to it and have a better understanding of this functionality they will have a better idea where to use these functions and get the optimum result.
Vertical Lookup: VLookup function helps to search for specific information located in a table or database.
The formula for Vlookup: =VLOOKUP(search_key, range, index, [is_sorted])
The search_key refers to the value you want to search for.
Next, range refers to the number of columns and rows that should be included in the search.
The index refers to the column index of the value to be returned, with the first column in the range being #numbered1. Take note that if you enter an index that’s isn’t between 1 and number of columns in the range, ‘#VALUE!’ will be returned.
Finally, is_sorted indicates whether the column to be searched (the first column of the specified range) is sorted. Google recommends that you set this to FALSE so that an exact match (NOT the nearest match) will be returned.
Horizontal Lookup: HLookup function is the same as VLookup, it just searches for horizontal information located in a table or database.
The formula for Hlookup: =HLOOKUP(search_key, range, index, [is_sorted])
The search_key is the value that the HLookup formula uses to search.
Next, range refers to the cell that we are performing a search on. The Sheets application always looks for the key in the first row of the range.
The index refers to the row index of the cell within the range, whose value the formula returns. The first row’s index within the range is #numbered1, the second in the range is #numbered2 and so on. For example, if we input 3 against this parameter, the HLOOKUP formula returns the cell value from the third row and the column in which it finds the search_key.
Finally, is_sorted an optional parameter which is TRUE by default. This indicates whether the first row in the range is sorted in ascending order or not. If it is not sorted, the value should be specified as FALSE.
One of the most important prerequisites for using Vlookup function is that the value the user is searching must be located in the left-most column of the table. In Hlookup functions, the value the user is searching must be located in the top-most column of the table.
