Look up values in a list of data

Suppose you want to look up an employee's phone extension by using his badge number, or the correct rate of a commission for a sales amount. You look up data to quickly and efficiently find specific data in a list and to automatically verify that you are using correct data. After you look up the data, you can perform calculations or display results with the values that were returned. There are several ways to look up values in a list of data and to display the results.

What do you want to do?

Look up values vertically in a list by using an exact match

Look up values vertically in a list by using an approximate match

Look up values vertically in a list of unknown size by using an exact match

Look up values horizontally in a list by using an exact match

Look up values horizontally in a list by using an approximate match

What happened to the Lookup Wizard?

Look up values vertically in a list by using an exact match

To do this task, you can use the VLOOKUP function, or a combination of the INDEX and MATCH functions.

Example that uses the VLOOKUP Function

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

1

2

3

4

5

6

7

8



9

A

B

C

D

Badge Number

Last Name

First Name

Extension

ID-34567

Davolio

Nancy

5467

ID-16782

Fuller

Andrew

3457

ID-4537

Leverling

Janet

3355

ID-1873

Peacock

Margaret

5176

ID-3456

Buchanan

Steven

3453

ID-5678

Suyama

Michael

428

Formula

Description (Result)

=VLOOKUP("ID-4537", A1:D7, 4, FALSE)

Lookup the badge number, ID-4537, in the first column and return the matching value in the same row of the fourth column (3355)

For more information about how to use this function, see VLOOKUP function.

Example that uses the INDEX and MATCH function

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

1

2

3

4

5

6


7

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)

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

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

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

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

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

For more information about how to use these functions, see INDEX function and MATCH function.

Top of Page

Look up values vertically in a list by using an approximate match

To do this task, use the VLOOKUP function.

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

Example

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

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

1

2

3

4

5

6

7




8

A

B

Frequency

Color

4.14

red

4.19

orange

5.17

yellow

5.77

green

6.39

blue

Formula

Description (Result)

=VLOOKUP(5.93, A1:B6, 2, TRUE)

Looks up 5.93 in column A, finds the next largest value that is less than 5.93 (5.77), and then returns the value from column B that is in the same row as 5.77 (green)

For more information about how to use this function, see VLOOKUP function.

Top of Page

Look up values vertically in a list of unknown size by using an exact match

To do this task, use the OFFSET and MATCH functions.

Use this approach 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 will be returned, and the first column isn't sorted alphabetically.

Example

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

1

2

3

4

5

6


7

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)

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

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

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

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

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

For more information about how to use these functions, see MATCH function and OFFSET function.

Top of Page

Look up values horizontally in a list by using an exact match

To do this task, use the HLOOKUP function.

Example

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

1

2

3

4

5

6


7

A

B

Bolts

Status

Axles

9

In stock

4

10

On order

5

11

Back order

6

Formula

Description (Result)

=HLOOKUP("Bolts", A1:C4, 3, FALSE)

Looks up Bolts in row 1, and returns the value from row 3 that's in the same column (10)

For more information about how to use this function, see HLOOKUP function.

Top of Page

Look up values horizontally in a list by using an approximate match

To do this task, use the HLOOKUP function.

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

Example

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

1

2

3







4

A

B

C

D

10000

50000

100000

Sales Volume

.05

.20

.30

Rate

Formula

Description (Result)

=HLOOKUP(78658,A1:D4,2, TRUE)

Looks up $78,658 in Row 1, finds the next largest value that is less than $78,658 ($50,000), and then returns the value from row 2 that is in the same column as $50,000 (20%)

Notes: 

  • You can display the rate and return number as a percentage. Select the cell, and then on the Home tab, in the Number group, click Percent Style Button image .

  • You can display the Sales Volume number as dollars. Select the cell, and then on the Home tab, in the Number group, click Accounting Number Format Accounting number format button .

For more information about how to use this function, see HLOOKUP function.

Top of Page

What happened to the Lookup Wizard?

This add-in is no longer included with Excel 2010. In earlier versions of Excel, you could use the Lookup Wizard to help you write formulas that would find a value at the intersection of a row and column by looking up values. This functionality has been replaced by the function wizard and the available Lookup and reference functions (reference).

Formulas that were generated by this wizard will continue to work in Excel 2010. You can edit them by using other methods.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×