7/26/2011

function Match and Choose excel

In Excel, the Choose function returns a value from a list of values based on a given position.
The syntax for the Choose function is:
Choose( position, value1, value2, ... value_n )
position is position number in the list of values to return. It must be a number between 1 and 29.
value1, value2, ... value_n is a list of up to 29 values. A value can be any one of the following: a number, a cell reference, a defined name, a formula/function, or a text value.

Note:
If position is less than 1, the Choose function will return #VALUE!.
If position is greater than the number of the number of values in the list, the Choose function will return #VALUE!.
If position is a fraction (not an integer value), it will be converted to an integer by dropping the fractional component of the number.

Applies To:
  • Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:
Let's take a look at an example:
=Choose(1, "Tech", "on", "the", "Net") would return "Tech"
=Choose(2, "Tech", "on", "the", "Net") would return "on"
=Choose(3, "Tech", "on", "the", "Net") would return "the"
=Choose(4, "Tech", "on", "the", "Net") would return "Net"
=Choose(5, "Tech", "on", "the", "Net") would return #VALUE!
=Choose(3.75, "Tech", "on", "the", "Net") would return "the"

VBA Code

The Choose function can also be used in VBA code. For example:
Dim LValue As String
LValue = Choose(1, "Tech", "on", "the", "Net")
In this example, the variable called LValue would contain "Tech" as a value.
In Excel, the Match function searches for a value in an array and returns the relative position of that item.
The syntax for the Match function is:
Match( value, array, match_type )
value is the value to search for in the array.
array is a range of cells that contains the value that you are searching for.
match_type is optional. It the type of match that the function will perform. The possible values are:
match_type Explanation
1
(default)
The Match function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order. If the match_type parameter is omitted, the Match function assumes a match_type of 1.
0 The Match function will find the first value that is equal to value. The array can be sorted in any order.
-1 The Match function will find the smallest value that is greater than or equal to value. You should be sure to sort your array in descending order.

Note:
The Match function does not distinguish between upper and lowercase when searching for a match.
If the Match function does not find a match, it will return a #N/A error.
If the match_type parameter is 0 and a text value, then you can use wildcards in the value parameter.
Wild card Explanation
* matches any sequence of characters
? matches any single character

Applies To:
  • Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:
Let's take a look at an example:

Based on the Excel spreadsheet above:
=Match(10572, A2:A5, 1) would return 3
(it matches on 10571 since the match_type parameter is set to 1)
=Match(10572, A2:A5) would return 3
(it matches on 10571 since the match_type parameter has been omitted and will default to 1)
=Match(10572, A2:A5, 0) would return #N/A
(it doesn't find a match since the match_type parameter is set to 0)
=Match(10573, A2:A5, 1) would return 4
=Match(10573, A2:A5, 0) would return 4

Let's take a look at how we can use wild cards in the Match function.

Based on the Excel spreadsheet above:
=Match("I?M", A2:A5, 0) would return 1
=Match("M*t", A2:A5, 0) would return 2
=Match("M?t", A2:A5, 0) would return #N/A

SHARE THIS

Author:

Tôi là PHƯỚC kỹ sư công nghệ thông tin. chuyên thiết kế web và làm dịch vụ MMO.

0 comments: