Please explain vlookup hlookup and xlookup with some examples

Gurudas 886 Reputation points
2022-03-25T11:09:30.747+00:00

Hello Team,

Greetings!

Please explain vlookup hlookup and xlookup via kb article along with some examples.

Product : Microsoft Office 365 Excel

Thank you in advance :)

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,669 questions
0 comments No comments
{count} votes

Accepted answer
  1. Emi Zhang-MSFT 23,031 Reputation points Microsoft Vendor
    2022-03-28T08:51:15.42+00:00

    Hi @GurudasSatardekar,

    VLOOKUP:https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

    VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

    Lookup_value is the value that needs to be looked up in the first column of the data table. Lookup_value can be a number, reference, or text string. When the first parameter of the vlookup function omits the lookup value, it means that the lookup value is 0.

    Table_array is the data table in which to find data. Use a reference to a range or range name.

    col_index_num is the serial number of the data column in table_array to search for data. When col_index_num is 1, returns the value of the first column of table_array, when col_index_num is 2, returns the value of the second column of table_array, and so on. If col_index_num is less than 1, the function VLOOKUP returns the error value #VALUE!; if col_index_num is greater than the number of columns in the table_array, the function VLOOKUP returns the error value #REF!.

    [range_lookup] is a logical value that indicates whether the function VLOOKUP looks for an exact match or an approximate match. If FALSE or 0, an exact match is returned, if not found, the error value #N/A is returned. If range_lookup is TRUE or 1, the function VLOOKUP will look for an approximate match, that is, if no exact match is found, it will return the largest number less than lookup_value. It should be noted that the search rule of the VLOOKUP function when performing approximate matching is to start from the first data, and if there is no matching value, it will continue to match the next value until it encounters a value greater than the search value, and then returns to the previous one. Data (the column where the lookup value is located should be sorted in ascending order for an approximate match). Defaults to 1 if range_lookup is omitted.

    Note: The most error-prone part of Vlookup is that the first column of the lookup area must contain the content of the lookup.

    For Example:
    187414-115.png

    HLOOKUP: https://support.microsoft.com/en-us/office/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f

    HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

    Lookup_value is the value that needs to be looked up in the first row of the data table. Lookup_value can be a number, reference, or text string.

    Table_array is the data table in which to find data. Use a reference to a range or range name.

    Row_index_num is the row number of the matching value to be returned in table_array. When Row_index_num is 1, it returns the value of the first row of table_array, when row_index_num is 2, it returns the value of the second row of table_array, and so on. If row_index_num is less than 1, the function HLOOKUP returns the error value #VALUE!; if the row_index_num is greater than the number of rows in the table_array, the function HLOOKUP returns the error value #REF!.

    Range_lookup is a logical value indicating whether the function HLOOKUP looks for an exact match or an approximate match. If TRUE or 1, an approximate match is returned. That is, if no exact match is found, the largest value less than lookup_value is returned. If range_lookup is FALSE or 0, the function HLOOKUP will look for an exact match, and if not found, return the #N/A error value. If range_lookup is omitted, it defaults to 0 (exact match).
    Tabular or numeric array (array: used to build a single formula that produces multiple results or operates on a set of parameters arranged in rows and columns. Array areas share a formula; array constants are a set of constants used as parameters .) finds the specified value and returns a value in the same column of the specified row in the table or array. Use the function HLOOKUP when the comparison value is in the first row of the data table and you want to find the data in the row given below. Use the function VLOOKUP when the comparison value is one column to the left of the data you are looking for. The H in HLOOKUP stands for "row".

    Note:
    If the function HLOOKUP cannot find lookup_value, and range_lookup is TRUE, use the largest value less than lookup_value. If the function HLOOKUP is less than the minimum value in the first row of table_array, the function HLOOKUP returns the #N/A error value.
    If range_lookup is FALSE and lookup_value is text, wildcards, question marks (?), and asterisks (
    ) can be used in lookup_value. The question mark matches any single character; the asterisk matches any sequence of characters. If you're looking for an actual question mark or asterisk, type a tilde (~) before the character.*

    For Example:
    187407-116.png

    XLOOKUP: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

    XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

    lookup_value Lookup value, if omitted, a blank cell is used for matching.

    lookup_array The array or range to search.

    return_array The array or range to return.

    [if_not_found] If no valid match is found, return the [if_not_found] text of your if_not_found. #N/A if no valid match is found and [if_not_found] is missing.

    [match_mode] specify the match type:
    0 - Exact match. Returns #N/A if not found. This is the default option.
    -1 - Exact match. If not found, return the next smaller item.
    1 - Exact match. If not found, return the next larger item.
    2 - Wildcard matching, where *, ? and ~ have special meaning.

    [search_mode] specify the search mode to use:
    1 - Search is performed starting from the first item. This is the default option.
    -1 - Perform a reverse search starting from the last item.
    2 - Perform a binary search that relies on the lookup_array being sorted in ascending order. Returns invalid results if not sorted.
    -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

    For Example:
    187376-117.png

    Please Note: NOT all versions of Excel can use XLOOKUP fucntion, the available versions: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.


    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful