In VLOOKUP or vertical lookup when we use a reference cell or value to search in a group of columns containing data to be matched and retrieve the output, the group of range we used to match is called as VLOOKUP table array, in VLOOKUP table array the referenced cell is at the leftmost side of the column.
Table Array in VLOOKUP Function In VLOOKUP or vertical lookup when we use a reference cell or value to search in a group of columns containing data to be matched and retrieve the output, the group of range we used to match is called as VLOOKUP table_array, in table array the referenced cell is at the leftmost side of the column.
HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row. Formula =HLOOKUP(value to look up, table area, row number)
In this example, the reference values are the names of the sales staff. If you have other data in this worksheet, such as additional data tables, leave at least one blank row on the bottom of the table and one blank column on the right of the table. This separates the lookup table from other data.
The table_array argument in the LOOKUP function always follows the lookup value.
Lookup_value: Required, represents the value that we want to look up for in the first column of a table or dataset.
The VLOOKUP function syntax has the following arguments: 1 Lookup_value: Required, represents the value that we want to look up for in the first column of a table or dataset. 2 Table_array: Required, represents the dataset or data array that is to be searched. 3 Col_indexnum: Required, represents the integer specifying the column number of the table_array that we want to return a value from 4 Range_lookup: Optional, represents or defines what the function should return in case it does not find an exact match to the lookup_value. This argument can be set to ‘FALSE; or ‘TRUE,’ where ‘TRUE’ indicating an approximate match (i.e., use the closest match below the lookup_value in case the exact match is not found), and ‘FALSE’ indicating an exact match (i.e., it returns an error in case the exact match is not found). ‘TRUE’ can also be substituted for ‘1’ and ‘FALSE’ for ‘0’.
On creating an HLOOKUP HLOOKUP Hlookup is a referencing worksheet function that finds and matches the value from a row rather than a column using a reference. Hlookup stands for horizontal lookup, in which we search for data in rows horizontally. read more or VLOOKUP function in Excel, we enter a range of cells as one of the arguments. This range is called the table_array argument.
In VLOOKUP or vertical lookup when we use a reference cell or value to search in a group of columns containing data to be matched and retrieve the output, the group of range we used to match is called as VLOOKUP table_array, in table array the referenced cell is at the leftmost side of the column.
Range_lookup: Optional, represents or defines what the function should return in case it does not find an exact match to the lookup_value. This argument can be set to ‘FALSE; or ‘TRUE,’ where ‘TRUE’ indicating an approximate match (i.e., use the closest match below the lookup_value in case the exact match is not found), and ‘FALSE’ indicating an exact match (i.e., it returns an error in case the exact match is not found). ‘TRUE’ can also be substituted for ‘1’ and ‘FALSE’ for ‘0’.
For the VLOOKUP function, the table_array must contain at least two columns of data.
Excel compares your reference value (lookup_value) with the data in the table and matches it, before scanning to your return value. If the value is not present, you will receive an #N/A error. However, even if your value is present in the table, there are still three possible reasons you could get an error:
In Excel, VLOOKUP scans the cell in the data table and looks for the data and the location you specify. When you hone in on the Hot menu, you see that there are three columns (sizes): 12 oz., 16 oz., and 20 oz. If you were ordering a 16-oz. chai latte, you would scan down the column of drinks until you find the chai latte, then across the row until you stop at the price in the appropriate (16 oz.) column. From there, you would determine that the drink costs $3.39. This process is not much different from what happens in Excel using the VLOOKUP function.
Respectively, these arguments in the VLOOKUP formula are lookup_value, table_array, col_index_num, and [range_lookup].
Excel data tables help you organize your data. After you build tables in Excel, they automatically resize and incorporate additional data in your formulas and keep your formulas consistent as they automatically populate. Named data tables give you an edge while building and locating formulas in large workbooks.
When you add the IF function to the VLOOKUP, the cell remains blank instead of showing an error if your VLOOKUP does not have a value to return.
In reality, very few VLOOKUP formulas are used to look up data in the same worksheet. Most VLOOKUPs pull data from another worksheet.
In VLOOKUP formulas, you can use wildcard characters to help look up values or spellings you are unsure of:
Lookup value (required argument) is the value that we want to look up in a table column. Where you want or get the value from another table.
Range lookup (optional argument) defines what this function should return if it does not find an exact match to the lookup value. The argument can be set to true or false, which means:
Vlookup function is very simple and easy to use. Let us understand the working of vlookup. Below mention are the details using the formulas.
When the user uses the vlookup function for finding specific information in an MS Excel spreadsheet, each matching information is displayed in the same row but in the next column. This function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index number position.
Master data table b, as above showing, is a table array, and in master data, you can see that mobile no column is on the 5 number column index. So then we need to put the 0 for exact matching or 1 for false matching.
The vlookup function returns result in any data type such as a string, numeric, date, etc.
Column index number (required argument) – an integer specifying the column number of the supplied table array that you want to return a value from. If you are only using it for data matching, you can put 1, but if you want to get a value from another column to match the lookup value, you need to put column no from matching column no.
The ‘Lookup_value’ should be the topmost row of the ‘table_array’ when we are using HLOOKUP. If we need to look somewhere else, then we must use another Excel formula.
The next is range_lookup. It makes HLOOKUP search for exact or approximate value. As we are looking out for an exact value, it would be False.
Let us consider the example below. The marks of four subjects for five students are as follows:
The next step would be to give the table array. Table array is nothing but rows of data in which the lookup value would be searched. Table array can be a regular range or a named range, or even an Excel table. Here we will give row A1:F5 as the reference.
HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row.
Ctrl + Shift + Enter will enclose the HLOOKUP formula in curly brackets. As shown below, all cells will give the results in one go. We will be saved from having to type the formula in each cell.
Here, it would be student D as we need to find his marks in Management. Now, remember that lookup_value can be a cell reference or a text string, or it can be a numerical value as well. In our example, it would be student name as shown below: