Training >> Browse Articles >> Applications
Training >> Browse Articles >> MS Office
How to Find Data in an Excel Table
This step-by-step article describes how to find data in a table (or range of cells) by using various built-in functions in Microsoft Excel. You can use different formulas to get the same result.
Create the Sample Worksheet
This article uses a sample worksheet to illustrate Excel’s built-in functions, for example referencing a name from column A and returning the age of that person from column C. To create this worksheet, enter the following data into a blank Excel worksheet.
You will type the value that you want to find into cell E2. You can type the formula in any blank cell in the same worksheet.
Term Definitions
This article uses the following terms to describe the Excel built-in functions:
Functions
LOOKUP()
The LOOKUP function finds a value in a single row or column and matches it with a value in the same position in a different row or column.
The following is an example of LOOKUP formula syntax: =LOOKUP
The following formula finds Mary’s age in the sample worksheet: =LOOKUP The formula uses the value “Mary” in cell E2 and finds “Mary” in the lookup vector (column A). The formula then matches the value in the same row in the result vector (column C). Because “Mary” is in row 4, LOOKUP returns the value from row 4 in column C (22).
Note The LOOKUP function requires that the table be sorted.
For more information about the LOOKUP function, click the following article number to view the article in the Microsoft Knowledge Base: 324986 (http://support.microsoft.com/kb/324986/) How to use the LOOKUP function in Excel
VLOOKUP()
The VLOOKUP or Vertical Lookup function is used when data is listed in columns. This function searches for a value in the left-most column and matches it with data in a specified column in the same row. You can use VLOOKUP to find data in a sorted or unsorted table. The following example uses a table with unsorted data.
The following is an example of VLOOKUP formula syntax: =VLOOKUP The following formula finds Mary’s age in the sample worksheet: =VLOOKUP The formula uses the value “Mary” in cell E2 and finds “Mary” in the left-most column (column A). The formula then matches the value in the same row in Column_Index. This example uses “3” as the Column_Index (column C). Because “Mary” is in row 4, VLOOKUP returns the value from row 4 in column C (22).
For more information about the VLOOKUP function, click the following article number to view the article in the Microsoft Knowledge Base: 181213 (http://support.microsoft.com/kb/181213/) How to Use VLOOKUP or HLOOKUP to find an exact match
INDEX
You can use the INDEX and MATCH functions together to get the same results as using LOOKUP or VLOOKUP.
The following is an example of the syntax that combines INDEX and MATCH to produce the same results as LOOKUP and VLOOKUP in the previous examples: =INDEX,Col_Index_Num)
The following formula finds Mary’s age in the sample worksheet: =INDEX,3) The formula uses the value “Mary” in cell E2and finds “Mary” in column A. It then matches the value in the same row in column C. Because “Mary” is in row 4, the formula returns the value from row 4 in column C (22).
Note If none of the cells in Lookup_Array match Lookup_Value (“Mary”), this formula will return #N/A.
For more information about the INDEX function, click the following article number to view the article in the Microsoft Knowledge Base: 324988 (http://support.microsoft.com/kb/324988/) How to use the INDEX function to find data in a table
OFFSET
You can use the OFFSET and MATCH functions together to produce the same results as the functions in the previous example.
The following is an example of syntax that combines OFFSET and MATCH to produce the same results as LOOKUP and VLOOKUP: =OFFSET,Offset_Col) This formula finds Mary’s age in the sample worksheet: =OFFSET,2) The formula uses the value “Mary” in cell E2 and finds “Mary” in column A. The formula then matches the value in the same row but two columns to the right (column C). Because “Mary” is in column A, the formula returns the value in row 4 in column C (22).
For more information about the OFFSET function, click the following article number to view the article in the Microsoft Knowledge Base: 324991 (http://support.microsoft.com/kb/324991/) How to use the OFFSET function
Back to more Microsoft Excel Help
