# Look up unique values

The custom function below will return unique items from a range, you just supply the range you want to get the unique items from and the index number for the unique item you want to return. You can also use the function to return the count of uniqe values/items in a range.

```Function UniqueItem(InputRange As Range, ItemNo As Long) As Variant
Dim cl As Range, cUnique As New Collection, cValue As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
End If
Next cl
UniqueItem = ""
If ItemNo = 0 Then
UniqueItem = cUnique.Count
Else
If ItemNo <= cUnique.Count Then
UniqueItem = cUnique(ItemNo)
End If
End If
On Error GoTo 0
End Function
```

Examples:

```Return the 2nd unique value in the range A1:A100:
=UniqueItem(A1:A100,2)

Return the count of unique values in the range A1:A100:
=UniqueItem(A1:A100,0)
```

# 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 ROW() or COLUMN() functions 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.

# Database functions

Excel has several database functions that can be used to analyze data stored in tables or databases. A table or a database is a range containing at least one column with data and two or more rows. The first row in each column contains the column heading, also called the fieldname. It’s common to define a name to the range containing the table or database, this makes it easier to understand the formulas later.
All of the database functions uses three arguments: database, field and criteria range.
The database functions are mostly named the same as the statistical functions, with the letter D in front of the function name, e.g. DSUM, DCOUNT.

All the database functions has the following syntax: Dfunction(database,field,criteriarange).
The argument database is filled in with the cell references or the userdefined name to the range that contains the data.
The argument field is filled in with the cell reference to the cell in the database that contains the field name you wish to use in the function. It’s also possible to use the field name as plain text surrounded by quotation marks ("). You can also use the field number, the first column in the table or database is field number 1, independent of which column number it has in the worksheet.

The argument criteriarange is filled in with the cell reference to the range in the worksheet that contains the criterias that is to be used by the function. The criteria range has to contain at least 1 column and 2 rows. The first row has to be filled in with field names from the database, or formulas that returns field names from the table or database. It’s not necessary to use all the field names from the database, only those field you want to use to perform the query are necessary. If you include a field name twice you can perform a query within an interval of the data values.

In the following example the database range is displayed with an yellow background color, the criteria range is displayed with a green background color. The formulas that are used is displayed as text in column C.

Example 1:
(Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how many values in column D that is greater than 250, together with the total sum for the values.
When you want to count something in a table or database you ought use a field that always will be filled in when you register a new record in the table or database.

Example 2:
(Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how you can perform a query within an interval by using a field name twice in the criteria range. The example shows how many dates in column C that is equal to or greater than 1.1.1950 and less than 1.1.1960, together with the total sum for for the values in column D.

Example 3:
(Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how you can use multiple rows in the criteria range to perform a more advanced query. The example shows how many names in column B the starts with NO or H, together with the total sum for the values in column D.

Example 4:
(Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how you can perform a query on nonblank fields (cells that are filled in). The example shows how many cells in column B that are filled in, together with the total sum for the values in column D.

Example 5:
(Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how you can perform a query on blank cells (empty cells). The example shows how many cells in column B that is empty, together with the total sum for the values in column D.