Look up values in your own tables

Excel has several functions that can return information from your own custom tables in many different ways. Here are some of the most common functions, followed by some examples on how to use them:

VLOOKUP(value,range,columnindex,sorted)

This function searches for a given value down the rows in the first column in the given range, and returns the content of the cell in the indicated column.
value : the value you want to find in the table, may be a string, number or a cell reference.
range : the address to the range containing the custom table, or a user-defined name.
columnindex : the column number in the table that you want to return the value from.
sorted : optional logical value, TRUE or FALSE. If left out the argument defaults to TRUE and the function assumes that the values in the first column in the table range is sorted in ascending order. If the argument is set to FALSE the content in the first column can be sorted in any order, and the function will return an error if the lookup value is not found.

Note! The argument sorted works only in Excel 5.0 or later. Older versions assumes that the values in the first column is sorted in ascending order.

HLOOKUP(value,range,rowindex,sorted)

This function searches for a given value from left to right in the first row in the given range, and returns the content of the cell in the indicated row.
value : the value you want to find in the table, may be a string, number or a cell reference.
range : the address to the range containing the custom table, or a user-defined name.
rowindex : the row number in the table that you want to return the value from.
sorted : optional logical value, TRUE or FALSE. If left out the argument defaults to TRUE and the function assumes that the values in the first row in the table range is sorted in ascending order. If the argument is set to FALSE the content in the first row can be sorted in any order, and the function will return an error if the lookup value is not found.

Note! The argument sorted works only in Excel 5.0 or later. Older versions assumes that the values in the first row is sorted in ascending order.

INDEX(range, rowindex,columnindex)

This function returns the value from the cell in the range according to the given row- and column indexes.

Examples:
Assume that you have the following table in a worksheet :

Function :	Returns :
`=VLOOKUP(200,A2:C6,2)`	Appelsiner
`=VLOOKUP(400,A2:C6,3)`	12
`=VLOOKUP(350,A2:C6,2)`	Bananer

If the lookup value is not found, the function returns the value from the largest value that is less than the lookup value. This can be useful when you want to look up values within preset intervals, e.g. a list of discount rates for different purchase amounts.

=VLOOKUP(350,A2:C6,2,FALSE) will return the error #N/A.
If you set the argument sorted to FALSE and the lookup value is not found, the function will return the error #N/A (not available).

=HLOOKUP("varenavn",A1:C6,3,FALSE) will return Appelsiner (oranges).

=HLOOKUP("varenavn",A1:C6,10,FALSE) will return the error #REF!
In this case the row reference is too large, the matrix contains only 6 rows, and it's not possible to return values from outside the given matrix.

=INDEX(A2:C6,2,3) will return 15.

The functions VLOOKUP() and HLOOKUP() can be used together with the functions ROW() or COLUMN() to avoid several manual corrections of row- or columnreferences when copying the lookup-functions.

Examples:
Select cell D5 and enter the following formula :
=VLOOKUP($A$1,$F$10:$O$100,COLUMN()-2,FALSE)
This formula can now be copied from cell D5 and all the way to cell L5. Now you will get in return the values from the table in the r F10:O100 (except the lookup values in the first column) matching the lookup value given in cell A1.

Document last updated 2000-02-05 12:44:52

User comments:

Ole P. from Norway wrote (2006-02-11 00:01:22 CET):

Re: How to lookup in many ranges
Depending on your source data, you can use several lookup functions, the database functions, use some form of array functions or you can use ADO and SQL queries.

Le Van Duyet from Viet Nam wrote (2006-02-10 23:30:07 CET):

How to lookup in many ranges (were named)
If I want to lookup in many ranges. How I have to do?
Thank you.

Le Van Duyet

Ole P. from Norway wrote (2005-08-22 14:55:06 CET):

Re: Limitation in VLookUp
You might be able to use the example A lookup function with a twist to create what you are looking for.

fy wrote (2005-08-19 19:05:23 CET):

Limitation in VLookUp
VLookup will stop at the first value it find and ignore the rest if there is another of the same value.
Is there a way to find two same value but with different content, eg. Product A with 20 units locate in Shelf A and Product A with 5 units locate in warehouse?

almaiz from St-Petersburg, Russia wrote (2004-12-02 08:40:29 CET):

Vlookup - simple replacement
Both Vlookup and Hlookup have a severe limitation: the value you look up is "sought" within the "range" boundaries. Using a combination of Match and index you can find a way around. Match will find out the exact value matching your lookup value then Index returns a corresponding value from the other reference range ( it can even be located in the different workbook ).

Ole P. from Norway wrote (2004-11-27 10:01:53 CET):

Re: What VBA Books Do You Recommend I Buy
Search Google Groups to find recommended VBA programming books.
I haven't read them all and can not recommend one...

al from Miami, Florida wrote (2004-11-27 09:46:25 CET):

What VBA Books Do You Recommend I Buy
What VBA books do you recommend I buy to apply VBA code to finance? Can you recommend me a book that is simple and not complicated and some online tutorials? Do you think online tutorials are better than buying books? Thank you!