Lookup Functions in Excel: Learn All 6 Functions

by

In this guide, we will be discussing the six most commonly used lookup functions in Excel: Vlookup, Hlookup, Xlookup, Index, Match, and Choose. These functions allow you to search and retrieve data from a specific table or range based on specific conditions or criteria.

Vlookup

The Vlookup (vertical lookup) function searches for a specific value in the first column of a table and returns a corresponding value in a specified column. It is one of the most commonly used lookup functions in Excel and is useful when you have a large amount of data and need to find specific information.

The syntax for the Vlookup function is as follows: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Where:

  • Lookup_value: This is the value you want to search for in the first column of the specified range.
  • Table_array: This is the range of cells that contains the lookup value and the data you want to retrieve.
  • Col_index_num: This is the column number within the table array that contains the data you want to retrieve.
  • [Range_lookup]: This is an optional argument that specifies whether you want an exact match (0) or an approximate match (1). If you leave this argument blank, it defaults to an exact match.

Step-by-Step Guide for Vlookup in Excel:

  1. Type “=Vlookup(” into a blank cell in your Excel sheet.
  2. In the parentheses, enter the value you want to search for. This is called the “lookup value.”
  3. Add a comma “,” and select the range of cells that contains the lookup value and the data you want to retrieve. This is called the “table array.”
  4. Add another comma “,” and specify the column number within the table array that contains the data you want to retrieve.
  5. Add another comma “,” and specify whether you want an exact match (FALSE) or an approximate match (TRUE). If you leave this argument blank, it defaults to an exact match.
  6. Close the parentheses and press “Enter.” The Vlookup function will now retrieve the data you specified from the specified column in the table array, based on the lookup value you entered.

In the example above, the function tells Excel to search for the value in cell A2 within the range J5:K8 and returns the corresponding value in the second column of the range with an exact match specified.

Hlookup

The Hlookup (horizontal lookup) function is similar to Vlookup but searches for data horizontally instead of vertically. It searches for a specific value in the first row of a table and returns a corresponding value in a specified row.

Syntax: =HLOOKUP(value, table, row, [exact match])

Where:

  • Value is the data you want to search for.
  • Table is the range of cells that contains the data you want to search.
  • Row is the row number in the table that contains the data you want to return.
  • [Exact match] is an optional argument that specifies whether you want an exact match (FALSE) or an approximate match (TRUE).

Step-by-step Guide for HLOOKUP in Excel:

  1. Type “=HLOOKUP(” in a cell to start the HLOOKUP formula.
  2. Specify the lookup value: In the first argument, specify the value that you want to search for in the top row of the specified range of cells. For example, “A2”.
  3. Insert a comma “,” and then specify the table array: In the second argument, specify the range of cells that contains the lookup value and the data you want to retrieve. For example, “A1:C10”.
  4. Insert another comma and specify the row index number: In the third argument, specify the row number within the table array that contains the data you want to retrieve. For example, “2”.
  5. Specify the range lookup: In the fourth argument, specify whether you want an exact match (FALSE ) or an approximate match (TRUE). If you leave this argument blank, it defaults to an exact match.
  6. Press enter to complete the formula and view the result.

In the example above, the function tells Excel to search for the value in cell A2 within the range K12:N13 and returns the corresponding value in the second row of the range with an exact match specified.

Xlookup

The Xlookup function is a new function in Excel that allows you to search for data in either a vertical or horizontal table. It has several advantages over the Vlookup and Hlookup functions, including the ability to search for data in either direction, better handling of errors, and improved performance.

Syntax: =Xlookup(value, lookup_array, result_array, [match_mode], [search_mode])

Where:

  • Value is the data you want to search for.
  • Lookup_array is the range of cells that contains the data you want to search.
  • Result_array is the range of cells that contains the data you want to return.
  • [Match_mode] is an optional argument that specifies whether you want an exact match (1) or an approximate match (0).
  • [Search_mode] is an optional argument that specifies whether you want to search vertically (1) or horizontally (2).

Step-by-Step Guide for Using the XLOOKUP Function in Microsoft Excel:

  1. Type “=XLOOKUP(” in a cell to start the XLOOKUP formula.
  2. Specify the lookup value: In the first argument, specify the value that you want to search for in the first column of the specified range of cells.
  3. Specify the lookup array: In the second argument, specify the range of cells that contains the lookup value and the data you want to retrieve.
  4. Specify the return array: In the third argument, specify the range of cells that contains the data you want to retrieve. This can be a different range from the lookup array.
  5. Specify the default value: In the fourth argument, specify the value that you want to return if the XLOOKUP function does not find a match for the lookup value. For example, “Not Found”.
  6. Specify the match mode: In the fifth argument, specify the type of match you want the XLOOKUP function to use. 0 for exact match, 1 for first value that is greater than or equal to the lookup value, -1 for first value that is less than or equal to the lookup value, and 2 for wildcard matching.
  7. Specify the search mode: In the sixth argument, specify the search direction for the XLOOKUP function. 0 for search from the first cell to the last cell, and 1 for search from the last cell to the first cell.
  8. Press enter to complete the formula and view the result.

Make sure to separate each argument with a comma.

In this example, the formula tells Excel to search for the value in cell A2 within the first column (J5:J8) of the specified range and return the corresponding value from the second column (K5:K8) or the specified “Not Applicable” value if no match is found, with a range match specified.

Match

The Match function searches for a specific value in a range of cells and returns the relative position of the first match. It is often used in combination with the Index function to search for specific data.

Syntax: =MATCH(value, lookup_array, [match_type])

Where:

  • Value is the data you want to search for.
  • Lookup_array is the range of cells that contains the data you want to search.
  • [Match_type] is an optional argument that specifies whether you want an exact match (1), an approximate match (0), or an exact match or the next smallest value (-1).

Step-by-Step Guide for Using the MATCH Function in Microsoft Excel:

  1. Type “=MATCH(” in a cell to start the MATCH formula.
  2. Specify the lookup value: In the first argument, specify the value that you want to search for in the specified range of cells. For example, “A2”.
  3. Specify the lookup array: In the second argument, specify the range of cells that contains the lookup value. For example, “A1:C10”.
  4. Specify the match type: In the third argument, specify the type of match you want the MATCH function to use. 0 for exact match, 1 for first value that is greater than or equal to the lookup value, and -1 for first value that is less than or equal to the lookup value. For example, “0”.
  5. Press enter to complete the formula and view the result.

In this example, the formula tells Excel to search for the value in cell A5 in the range J5 to J8 and returns the relative position of the first match in the range.

Index

The Index function returns the value of a cell in a specified row and column within a table. It is often used in combination with the Match function to search for specific data.

Syntax: =INDEX(array, row_num, [column_num])

Where:

  • Array is the range of cells that contains the data you want to search.
  • Row_num is the row number in the array that contains the data you want to return.
  • [Column_num] is an optional argument that specifies the column number in the array that contains the data you want to return.

Step-by-Step Guide for Using the INDEX Function in Microsoft Excel:

  1. Type “=INDEX(” in a cell to start the INDEX formula.
  2. Specify the array: In the first argument, specify the range of cells that you want to return a value from.
  3. Specify the row number: In the second argument, specify the row number in the array from which you want to return a value.
  4. Specify the column number: In the third argument, specify the column number in the array from which you want to return a value. .
  5. Press enter to complete the formula and view the result.

This tells Excel to return the value in the cell at the intersection of the row specified in cell E18 and the second column of the range A18 to B29 using the INDEX function.

Choose

The Choose function returns a value from a list based on a specified index number. It allows you to select a value from a list of options based on a specified index number.

Syntax: =CHOOSE(index_num, value1, value2, …)

Where:

  • Index_num is the index number that specifies which value you want to return.
  • Value1, value2, … are the values you want to choose from.

Step-by-Step Guide for Using the CHOOSE Function in Microsoft Excel:

  1. Type “=CHOOSE(“ in a cell to start the CHOOSE formula.
  2. Specify the index number: In the first argument, specify the index number that you want to use to select a value from the list of values. The index number must be a positive integer. For example, “3”.
  3. Specify the values: In the following arguments, specify the list of values from which you want to choose a value. For example, “1”, “2”, “3”, “4”.
  4. Press enter to complete the formula and view the result.

In the example above, this tells Excel to return the value corresponding to the index specified in cell E18 from the list of values in cells B19 to B29.

Conclusion

These six lookup functions are powerful tools that can help you retrieve and manipulate data from a spreadsheet. Each of these functions has its own specific syntax and usage, but they all allow you to quickly find information based on specific criteria, making them essential tools for data analysis and management.

Related Posts: