Functions of Excel Vlookup Lookup and Hlookup

Function of excel Vlookup allows you to quickly find matching data or information in a large table or data set. When searching for data in a table or range by row, use VLOOKUP. Find an employee’s name by employee ID, or check the price of an auto part by part number.

You’ll need these four pieces of data to construct the VLOOKUP syntax:

• The desired value, often known as the “lookup value.”
• The bounds within which to locate the value to be looked up. Keep in mind that VLOOKUP relies on the lookup value being in the first column of the range. If the value you’re looking for is in cell C2, your range, for instance, should begin with C.
• The index of the range column containing the result. If the specified range is B2:D11, then B2 is the first column, C2 is the second, and so on.
• If you desire a close match for the return value, set this to TRUE; otherwise, set it too FALSE. If you don’t enter a value, “TRUE” or “approximate match” will be assumed.

Essentials of the VLOOKUP Function to Keep in Mind:

The Excel VLOOKUP Function is described here, along with some key points to keep in mind:

1. If range lookup is not specified, VLOOKUP will use the closest match even if it is not an exact match.
2. The function’s major drawback is that it never looks wrong. Information from the table rows to the right of the first column will be retrieved.
3. VLOOKUP only considers the first value to be a match if the lookup column contains duplicates.
4. There is no regard for the case while using this function. Subtotal function in excel is also available.

HLOOKUP:

The HLOOKUP function can search for a specific value in a horizontal table and return it. Like “V” in “vertical” in “VLOOKUP,” “H” in “horizontal” in “HLOOKUP.” The lookup values need to start in the leftmost column of the table and go across the top as you read it. You can use wildcards (*?) with HLOOKUP to identify partial excel match function in addition to precise and approximate matching.

Excel’s XLOOKUP:

Find data in a table or range by row using the XLOOKUP function. Find an employee’s name using their ID number, or check the price of a car part using the part number. XLOOKUP allows you to perform a search in one column and retrieve a matching row from another column, regardless of the direction the return column is facing.

Excel’s Countifs feature:

Excel if function or Countifs function in excel is used to count cells that satisfy several conditions at once. Dates, strings, numbers, expressions, cell references, and formulas might all be used as part of the criteria. This excels index function evaluates cells in a given range against the specified criteria and returns the total number of occurrences.

Conclusion:

COUNTIFS is helpful when we want to apply several criteria to one or more ranges, while COUNTIF is helpful when we just want to apply one criterion to one range. If the values you need to lookup are in the very first row of a table, use HLOOKUP.