Simplifying Excel Lookup

Functions: VLOOKUP,

HLOOKUP, INDEX, MATCH,

CHOOSE, and OFFSET

The difference between Excel reference functions VLOOKUP, HLOOKUP, INDEX, MATCH, CHOOSE, and OFFSET is often not clear to every Excel user. This article will illustrate the most basic use of each of these lookup function formulas so that the differences and most common use of each is clear and more intuitive. Each one of these lookup functions will be used to look up the same item in the same array in order to illustrate the differences between the formulas.

VLOOKUP

VLOOKUP is used most often to look up an item in a table that has row headers. VLOOKUP looks up the correct row header and then locates the desired item in the specified column of that row. VLOOKUP looks up and down vertically for the correct row header. In its simplest form, VLOOKUP can be implemented with the following formula:

VLOOKUP( Row Header, Array, Column Number)

(Click On Image To See a Larger Version)

HLOOKUP

HLOOKUP is used most often to look up an item in a table that has column headers. HLOOKUP looks up the correct column header and then locates the desired item in the specified row of that column. HLOOKUP looks back and forth horizontally for the correct column header. In its simplest form, HLOOKUP can be implemented with the following formula:

HLOOKUP( Column Header, Array, Row Number)

(Click On Image To See a Larger Version)

INDEX

INDEX is best used to look up an item in a table when the item’s relative position in the table (row number and column number relative to the upper left corner of the table) is known. In its simplest form, INDEX can be implemented with the following formula:

INDEX( Array, Row Number, Column Number )

(Click On Image To See a Larger Version)

CHOOSE

CHOOSE returns a value from a list that is in the list position specified by the formula. In its simplest form, CHOOSE can be implemented with the following formula:

CHOOSE( List Position, List Item 1 Address, List Item 2 Address, List Item 3 Address, … )

(Click On Image To See a Larger Version)

It can also be implemented by the following formula:

CHOOSE( List Position, List Item 1, List Item 2, List Item 3, … , List Item n )

(Click On Image To See a Larger Version)

OFFSET

OFFSET returns in item the cell that is a certain number of rows below and columns to the right of a reference cell. In its simplest form, OFFSET is implemented with the following formula:

OFFSET( Reference Cell, Rows Down, Columns to the Right )

(Click On Image To See a Larger Version)

MATCH

MATCH returns the relative list position of a specified item in a list. In its simplest form, MATCH is implemented with the following formula:

MATCH ( Item, array, 0) 0 indicates that there must be an exact of the item in the list for a position to be returned.

(Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!