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 specifi ed value
in a specifi 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 fi nal argument of MATCH is 0, then it fi nds an exact match from an unsorted
list, similar to using FALSE in VLOOKUP. If the fi nal argument is 1, then MATCH
will fi 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 fi nal argument and MATCH will fi 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 fi 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 fi 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