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, you want to find the commission rate for a given sales amount. There are several ways to look up these values quickly and efficiently.

For example, you might want to look up values vertically in a list by using an exact match. To do this, you can use the VLOOKUP function or a combination of the INDEX function and MATCH function, as shown in the following examples.

This workbook shows you how VLOOKUP looks up values that are an exact match.

This workbook shows you how INDEX and MATCH look up values that are an exact match.

This workbook shows you how VLOOKUP looks up values by using an approximate match. For example, when you know the frequency and want to look up the associated color, as shown in this workbook.

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

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

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.

This workbook shows you how OFFSET and MATCH lookup values.

Look up values horizontally

Use the HLOOKUP function to lookup values horizontally in a list by using an exact match.

This workbook shows you how HLOOKUP looks up values horizontally.

This workbook shows you how HLOOKUP uses an approximate match when the values in the first row are sorted in ascending order.

Tip:  In your worksheet, you can show formula results as percentages or currency. In edit mode, select the cell, click Home > Number Format, and pick Percentage or Currency.