Microsoft KB Archive/324861

= How to find data in an Excel table =

Article ID: 324861

Article Last Modified on 11/13/2006

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q324861



IN THIS TASK

 * SUMMARY
 * Create the Sample Worksheet
 * Term Definitions
 * Functions
 * LOOKUP
 * VLOOKUP
 * INDEX and MATCH
 * OFFSET and MATCH



SUMMARY
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.

back to the top

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.

back to the top

Term Definitions
This article uses the following terms to describe the Excel built-in functions:

back to the top

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(E2,A2:A5,C2:C5)

The formula uses the value &quot;Mary&quot; in cell E2 and finds &quot;Mary&quot; in the lookup vector (column A). The formula then matches the value in the same row in the result vector (column C). Because &quot;Mary&quot; 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 How to use the LOOKUP function in Excel

back to top

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(E2,A2:C5,3,FALSE)

The formula uses the value &quot;Mary&quot; in cell E2 and finds &quot;Mary&quot; in the left-most column (column A). The formula then matches the value in the same row in Column_Index. This example uses &quot;3&quot; as the Column_Index (column C). Because &quot;Mary&quot; 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 How to Use VLOOKUP or HLOOKUP to find an exact match

back to the top

INDEX and MATCH
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( ,MATCH(, ,0), )

The following formula finds Mary's age in the sample worksheet:

=INDEX(A2:C5,MATCH(E2,A2:A5,0),3)

The formula uses the value &quot;Mary&quot; in cell E2and finds &quot;Mary&quot; in column A. It then matches the value in the same row in column C. Because &quot;Mary&quot; is in row 4, the formula returns the value from row 4 in column C (22).

Note If none of the cells in  match   (&quot;Mary&quot;), 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 How to use the INDEX function to find data in a table

back to the top

OFFSET and MATCH
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( ,MATCH(, ,0), )

This formula finds Mary's age in the sample worksheet:

=OFFSET(A1,MATCH(E2,A2:A5,0),2)

The formula uses the value &quot;Mary&quot; in cell E2 and finds &quot;Mary&quot; in column A. The formula then matches the value in the same row but two columns to the right (column C). Because &quot;Mary&quot; 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 How to use the OFFSET function

back to the top

Additional query words: XL2007 XL2003 XL2002 XL2000

Keywords: kbhowtomaster kbhowto KB324861

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.