|
These pages are no longer updated and are only available for archive purposes.
Click here to visit the pages with updated information.
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
Printerfriendly version
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!
|
|
|