Match Formula in Excel
The Match Formula in Excel is an easy way to have excel look through an array of values and return the relative position if the match is found. The difference between this and the lookup functions (eg. Vlookup) is it return the position of the item and not the item itself. Let’s go ahead and take a look at the formula:
Match(lookup_value, lookup_array, match_type)
where lookup_value is the value you are looking up against the array, lookup_array is the range of values you are trying to match against, and match_type tells the formula how it should do the match. Match_type can be one of three values:
1. -1 (largest value less than or equal to lookup_value, array must be in ascending order),
2. 0 (exact match of the lookup_value, array does not need to be ordered),
3. 1 (smallest value greater than or equal to lookup_value, array must be in descending order).
Rank Formula in Excel
The Rank Formula that is built into Excel can be a very useful way of speeding up the ranking of several items. Let’s take a look at the formula and see how this can help us out:
=rank(number, ref, order)
where number is the number whose rank you want to find; ref is a reference to the range of numbers you want to rank against (non-numeric values in the range are ignored), and order refers to the way you want to ranking to appear – 0 or blank for descending and a non-zero number for ascending.
Hyperlink Formula in Excel
I’m going to add this section to the Microsoft Excel Help section (menu on the left) of the website. An excel user contacted me and questioned how to create a hyperlink inside of excel using the value in another cell to as part of the link. The Formula looks like this:
HYPERLINK(link_location,friendly_name)
where link_location is where you want the hyperlink to go to and friendly_name is how the link will appear on your sheet.
For example, if you have a file on your desktop it would look something like this:
Hyperlink(“C:\Documents and Settings\Admin1\Desktop\Example.xls”,”Example Link”)
Today() Formula in Excel
A common excel formula that can come in handy in many projects with Excel is the Today() Function. The Today() Function is used to return the serial number of the current date. By formatting the cell where the formula is used, it will return the Month, Day and Year of today. One of the easiest functions in Excel to use, here is the formula:
=Today()
Since today is June 5, 2007, the formula will return 6/5/07 since it is automatically formatted in date format. If you were to change the formatting to a number, the formula would return the serial number 39238 (If you are wondering what this number stands for, it is the number of days from January 1, 1900 – Tomorrow the serial number returned would be 39239)
There are many places in your projects you will be using the Today() function, from showing the date your spreadsheets were last updated to calculated how many days from today a deadline is. Once you learn to use this function regularly it will be a function you can’t live without.
Over the next few posts, I will be adding many example of how to use the numerous date and time functions built into excel. Please contact us with any questions or suggestions you may have.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
ISNA, ISERROR, and ISBLANK Formula in Excel
Excel has many built-in formulas that will check for errors for you. Were going to take a look at three of those formulas, which are ISNA, ISBLANK and ISERROR. Let’s start by taking a look at the formulas:
ISNA(value)
ISERROR(value)
ISBLANK(value)
Each of these formulas looks at value and evaluatues whether it is #N/A (with ISNA), #Value (with ISERROR), or blank (with ISBLANK).
When You Can Use These Formulas
These formulas come in very handy when you are working with a large group of formulas and need further analysis of the results. Having and #N/A or #Value in your results, as I’m sure you may have experienced, will most likely result in receiving more errors the more that particular result is analyzed. As you’ll see in the examples below, vlookup is a very common formula which will return an #N/A if the lookup value is not found. Or if you try to add a string to a number, the result will return #value.
Loading ...