Microsoft Office Tutorials and References
In Depth Information
APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE
LOWER(text) [Category: Text]
Converts all uppercase letters in a text string to lowercase. Guru Tip: great
function for eliminating case differences between values. Also see UPPER and
PROPER.
MATCH(lookup_value,lookup_array,match_type) [Category: Lookup &
Reference]
Returns the relative position of an item in an array that matches a speciﬁ ed value
in a speciﬁ ed order. Use MATCH instead of one of the LOOKUP functions when
you need the position of an item in a range instead of the item itself. Guru Tip: If
the ﬁ nal argument of MATCH is 0, then it ﬁ nds an exact match from an unsorted
list, similar to using FALSE in VLOOKUP. If the ﬁ nal argument is 1, then MATCH
will ﬁ nd the value equal to or just larger the lookup value from a sorted list, the
same as using TRUE in VLOOKUP. MATCH offers further functionality in that
you can specify -1 as the ﬁ nal argument and MATCH will ﬁ nd a value equal to or
just smaller than the lookup value from an unsorted list. This option is beyond the
powers of VLOOKUP. Also, the function can ﬁ nd a value from a single column
lookup-array like VLOOKUP or from a single-row lookup_array like HLOOKUP.
The one strange thing about MATCH is that it returns the relative position of the
match within the list. In the image below, Green Bay is the 7th city in A10:A19
so the formula in E5 returns a 7. For a long time, I could never ﬁ gure out why
I would care to know that an item is the nth item in a list. My managers never
ask me, "Hey Bill, what relative row is that account found on?". The power is to

Search JabSto ::

Custom Search