How to Use Lookups and Reference Functions in Excel 2016

Edited 4 months ago by ExtremeHow Editorial Team

Microsoft OfficeExcelLookupsReference FunctionsDataSpreadsheetCalculationFormulasWindowsMacProductivity

Microsoft Excel 2016 is a powerful tool for data analysis and management. One of its key features is the set of functions that allow users to easily lookup and reference data. This guide provides detailed information on how to use the lookup and reference functions in Excel 2016. These functions help users find specific values within a spreadsheet, manage data across multiple sheets, and even automate some complex data manipulation tasks.

Understanding the Lookup function

Lookup functions in Excel are designed to help you quickly find information stored in a spreadsheet. These functions are useful when working with large sets of data, as they allow you to find specific information without having to search manually. The most common lookup functions are VLOOKUP, HLOOKUP, LOOKUP, and INDEX and MATCH.

VLOOKUP function

VLOOKUP function is probably one of Excel's most used functions. It stands for "vertical lookup," which allows users to find a value in the first column of a table and then return the value in the same row from a specified column.

The syntax of the VLOOKUP function is as follows:

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

• lookup_value: The value you want to search for.
• table_array: The range of cells that makes up the table array.
• col_index_num: The column number in the table array from which you want to retrieve data.
• range_lookup: A logical value that determines whether to find an approximate match (true) or an exact match (false).

Consider the following example:

=VLOOKUP("Banana", A1:C10, 2, FALSE)

In this example, Excel searches for the word "Banana" in the first column of the range A1:C10 and returns the value from the second column of the found row. If no exact match is found for "Banana", Excel returns an error value.

The HLOOKUP function

HLOOKUP function works in the same way as VLOOKUP, but it searches a row instead of a column, so performs a "horizontal lookup." It is especially useful for data presented horizontally.

The syntax of the HLOOKUP function is:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: The value you want to search for.
• table_array: The range of cells containing the data.
• row_index_num: The row number in the table array from which the value is to be returned.
• range_lookup: A logical value that specifies whether to search for an exact match (FALSE) or an approximate match (TRUE).

Example:

=HLOOKUP(1020, A1:F2, 2, FALSE)

This example will search for the value 1020 in the first row of the range A1:F2 and return the value found in the same column in the second row.

Lookup function

LOOKUP function in Excel can perform both horizontal and vertical lookups, but it is not as commonly used due to its limitations compared to VLOOKUP and HLOOKUP. LOOKUP function has two syntax forms: Vector and Array.

Vector form:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value: The value you want to combine into the vector.
• lookup_vector: A single row or column lookup vector.
• result_vector: Optional. A single row or column result vector from which to obtain the result.

Array form:

=LOOKUP(lookup_value, array)

• lookup_value: The value you want to match in the table.
• Table: A range of values composed of at least two columns.

Example:

=LOOKUP("dog", A1:A3, B1:B3)

This example finds the word "dog" in the range A1:A3 and returns the value from the corresponding cell in B1:B3.

Understanding reference works

Reference functions in Excel help you work effectively with cell references and ranges. These functions are especially useful in complex spreadsheets where you are referencing multiple data sources. The most important reference functions include INDEX, MATCH, ADDRESS, INDIRECT, OFFSET, and more.

Index function

INDEX function returns the value or reference of the cell at the intersection of a row and column in a given range or array. The function can be used in a variety of ways, particularly combined with MATCH function to achieve more complex lookups.

The syntax of the INDEX function is:

=INDEX(array, row_num, [column_num])

• array: A range or array constant.
• row_num: The row number in the table from which to retrieve the value.
• column_num: Optional. The column number to return when the array has more than one column.

Example:

=INDEX(A1:C3, 2, 3)

This example will return the values in the second row and third column of the specified range A1:C3.

Match function

MATCH function searches for a specified item in a range of cells and then returns the relative position of that item within the range. It is mainly used with INDEX for advanced lookup operations.

The syntax of the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value you want to search for.
• lookup_array: The range of cells in which to find the search value.
• match_type: Optional. The number -1, 0, or 1. This depends on how you want to search.

• If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The data must be in ascending order.
• If match_type is 0, MATCH finds the first value exactly equal to lookup_value. The data can be in any order.
• If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The data must be in descending order.

Example:

=MATCH(39, A1:A5, 0)

This example will return the position of the value 39 within the range A1:A5. If 39 is located in the third cell, the function returns the number 3.

Using INDEX and MATCH together

The combination of INDEX and MATCH functions is a powerful alternative to VLOOKUP or HLOOKUP because of its flexibility. Unlike VLOOKUP, which can only search in the leftmost column, INDEX and MATCH can search through any column or row.

Example:

=INDEX(B1:B10, MATCH("Orange", A1:A10, 0))

In this example, Excel uses MATCH to search for "orange" in the range A1:A10, finds its position, then uses INDEX to return the corresponding value from B1:B10.

Address function

ADDRESS function returns a reference as text for a specified row and column number. This can be useful when you need a reference in string format.

The syntax of the ADDRESS function is:

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

• row_num: The row number used in the cell reference.
• column_num: The column number used in the cell reference.
• abs_num: Optional. A number (1 to 4) specifying the reference type.
• a1: Optional. Logical value specifying the reference style.
• sheet_text: Optional. The name of the worksheet to use.

Example:

=ADDRESS(2, 3)

This example will return $C$2, which is an absolute reference to the cell located in the second row and third column.

Indirect functions

INDIRECT function returns the reference specified by a text string. This can be especially useful when creating dynamic cell references in a workbook.

The syntax of the INDIRECT function is:

=INDIRECT(ref_text, [a1])

• ref_text: A reference to a cell that includes A1 style reference, R1C1 style reference, a name defined as a reference, or a reference to a cell as a text string.
• a1: Optional. A logical value that specifies what type of reference is contained in the cell reference argument.

Example:

=INDIRECT("A" & 1)

This example uses INDIRECT function to return the value located in cell A1.

Offset function

OFFSET function creates a reference that is offset by a specified number of rows and columns from a cell or range of cells, based on a starting point.

The syntax of the OFFSET function is:

=OFFSET(reference, rows, cols, [height], [width])

• Reference: The reference from which you want to base the offset. This must refer to a cell or a range of adjacent cells.
• Lines: The number of rows above or below that you want to refer to the upper-left cell.
• cols: The number of columns to the left or right that you want the top-left cell of the reference to refer to.
• height: Optional. The height, in number of rows, that you want the returned reference to have.
• width: Optional. The width, in number of columns, that you want in the returned reference.

Example:

=OFFSET(A1, 3, 2)

This example returns the cell reference that is 3 rows down and 2 columns to the right from cell A1.

Conclusion

VLOOKUP, HLOOKUP, and other lookup and reference functions turn Excel 2016 into an incredibly powerful tool for data analysis and manipulation. By harnessing the power of these functions, you can efficiently manage and reference data in a sheet or workbook, perform advanced data searches, and automate complex calculations.

Mastering these functions requires understanding their syntax, how they interact, and what scenarios they apply to. Practice using these functions in different combinations to gain insight into how they work together to create powerful, dynamic spreadsheets.

  Share This Article

If you find anything wrong with the article content, you can request an update