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