Look up values in a range

Microsoft Office Excel 2003

previous page next page

The Lookup Wizard creates the lookup formula based on a worksheet data that has row and column labels. The Lookup Wizard helps you find other values in a row when you know the value in one column, and vice versa. The Lookup Wizard uses INDEX and MATCH in the formulas it creates.

  1. On the Tools menu, click Add-ins, select the Lookup Wizard box, and then click OK.

  2. Click a cell in the range.
  3. On the Tools menu, click Lookup.
  4. Follow the instructions in the wizard.

ShowLook up values by comparing them with the values in the first column

Use the VLOOKUP function to do this task.

Important This method only works if the values in the first row or column have been sorted in ascending order.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

In this example, you know the frequency and want to look up the associated color.

 
1
2
3
4
5
6
A B
Frequency Color
4.14 red
4.19 orange
5.17 yellow
5.77 green
6.39 blue
Formula Description (Result)
=VLOOKUP(5.77, A1:B6, 2) Looks up 5.77 in column A, and returns the value from column B that's in the same row (green)

Function details

VLOOKUP

ShowLook up values by comparing them with the values in the first row

Use the HLOOKUP function to do this task

Important This method only works if the values in the first row or column have been sorted in ascending order.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Show How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
A B C
Status Axles Bolts
In stock 4 9
On order 5 10
Back order 6 11
Formula Description (Result)
=HLOOKUP("Bolts", A1:C4, 3) Looks up Bolts in row 1, and returns the value from row 3 that's in the same column (10)

Function details

HLOOKUP

ShowLook up values in a range that isn't sorted

Use the INDEX and MATCH functions to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
5
A B
Product Count
Bananas 38
Oranges 25
Apples 41
Pears 40
Formula Description (Result)
=INDEX(A2:B5,MATCH("Pears",A2:A5,0),2) Looks up Pears in column A and returns the value for Pears in column B (40).

The formula uses the following arguments.

Formula to look up a value in an unsorted list

Formula to look up a value in an unsorted range (INDEX function)

Callout 1 A2:B5: The entire range in which you are looking up values

Callout 2 MATCH("Pears",A2:A5,0): The MATCH function determines the row number

Callout 3 "Pears": The value to find in the lookup column.

Callout 4 A2:A5: The column for the MATCH function to search.

Callout 5 2: The column from which to return the value. The leftmost column is 1.

Function details

INDEX

MATCH

ShowLook up values in a range of uncertain size that isn't sorted

Use the OFFSET and MATCH functions to do this task.

Use this process when your data is in an external data range that you refresh each day. You know the price is in column B, but you don't know how many rows of data the server will return, and first column isn't sorted alphabetically.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
5
A B
Product Count
Bananas 38
Oranges 25
Apples 41
Pears 40
Formula Description (Result)
=OFFSET(A1,MATCH("Pears",A2:A5, 0),1) Looks up Pears in column A and returns the value for Pears in column B ( 40).

The formula uses the following arguments.

Formula to look up a value in an unsorted list (OFFSET function)

Callout 1 A1: The upper left cell of the range, also called the starting cell.

Callout 2 MATCH("Pears",A2:A5, 0): The MATCH function determines the row number below the starting cell to find the look up value.

Callout 3 "Pears": The value to find in the lookup column.

Callout 4 A2:A5: The column for the MATCH function to search. Don't include the starting cell in this range.

Callout 5 1: The number of columns to the right of the starting cell to find the lookup value.

Function details

MATCH

OFFSET

previous page start next page