Friday, April 29, 2005
Excel: INDEX Function
The Excel Index function is used in a similar way to how you would use a grid reference on a map.
Given the row number and the column number you know exactly which cell you are looking at or referencing. The grid structure of Excel naturally makes the Index function quite intuitive.
The syntax of the Excel Index function can take two forms. These are either:
=INDEX(Array,Row_num,Column_num)
where
Array is the range of cells that you are working with
Row_num indicates the row you wish to use in your reference, and
Column_num indicates the column you wish to use in your reference
or
=INDEX(Reference,Row_num,Column_num,Area_num)
where
Reference is a reference to one or more cell ranges,
Row_num indicates the row you wish to use in your reference,
Column_num indicates the column you wish to use in your reference, and
Area_num indicates which area within Reference that you wish to use
To clear up any confusion, use the first form of the formula if the range of cells you are working with are contained within one single block, and use the second form when the cells you are working with are split into several individual blocks.
http://www.excelonlinetraining.com/excel-index-function.html
Given the row number and the column number you know exactly which cell you are looking at or referencing. The grid structure of Excel naturally makes the Index function quite intuitive.
The syntax of the Excel Index function can take two forms. These are either:
=INDEX(Array,Row_num,Column_num)
where
Array is the range of cells that you are working with
Row_num indicates the row you wish to use in your reference, and
Column_num indicates the column you wish to use in your reference
or
=INDEX(Reference,Row_num,Column_num,Area_num)
where
Reference is a reference to one or more cell ranges,
Row_num indicates the row you wish to use in your reference,
Column_num indicates the column you wish to use in your reference, and
Area_num indicates which area within Reference that you wish to use
To clear up any confusion, use the first form of the formula if the range of cells you are working with are contained within one single block, and use the second form when the cells you are working with are split into several individual blocks.
http://www.excelonlinetraining.com/excel-index-function.html
Excel HLOOKUP Function
The HLOOKUP function works exactly the same way as the VLOOKUP function but turned clockwise 90 degrees.
HLOOKUP reads across the first or uppermost row of data searching for the column that contains the value you have specified.
When it finds the specified value it then moves down the rows and returns the value from the row number that you specified.
Let's look at an example of the Excel HLOOKUP function as this will make it clearer.
In this simple table we have the field names in the first four rows and each record is kept in a single column.
We would like to type in a client's name and have returned that client's occupation.
The syntax for the HLOOKUP function is
=HLOOKUP(Lookup_value,Table_array,Row_index_num,Range_lookup)
where
Lookup_value is the value you are looking for in the first row of your array
Table_array is the range of cells you are searching
Row_index_num is the rows number from which the value should be returned
Range_lookup is an optional argument. Use FALSE to ensure you find an exact match or TRUE to find the nearest value. If omitted then TRUE is the default value)
http://www.excelonlinetraining.com/excel-HLOOKUP-function.html
HLOOKUP reads across the first or uppermost row of data searching for the column that contains the value you have specified.
When it finds the specified value it then moves down the rows and returns the value from the row number that you specified.
Let's look at an example of the Excel HLOOKUP function as this will make it clearer.
In this simple table we have the field names in the first four rows and each record is kept in a single column.
We would like to type in a client's name and have returned that client's occupation.
The syntax for the HLOOKUP function is
=HLOOKUP(Lookup_value,Table_array,Row_index_num,Range_lookup)
where
Lookup_value is the value you are looking for in the first row of your array
Table_array is the range of cells you are searching
Row_index_num is the rows number from which the value should be returned
Range_lookup is an optional argument. Use FALSE to ensure you find an exact match or TRUE to find the nearest value. If omitted then TRUE is the default value)
http://www.excelonlinetraining.com/excel-HLOOKUP-function.html
Excel VLOOKUP Function
Excel VLOOKUP Function: "The VLOOKUP function is likely to become very familiar to you if you use Excel for any length of time. I personally can't think of a day when I haven't used the Excel VLOOKUP function.
Along with SUM and SUMIF, VLOOKUP is one of the most used Excel functions.
The VLOOKUP function works by searching down the leftmost column of your data and then reads across the row to find the value in the column you specify.
Clear as mud isn't it?
If you think of the process as similar to that which you would adopt when searching for a phone number in a directory, you won't go far wrong.
In fact, let's use the phone directory scenario to illustrate the Excel VLOOKUP function in action.
Below is a list of names and their corresponding phone numbers. We would like to be able to type in a name and have the phone number automatically returned.
For this particular example I am going to use two cells, one for inputting the name of the person who's number I want, and the second cell will be where I enter the VLOOKUP function which will return the phone number.
Click in cell B12 and type 'David Beckham'. (without the quotes)
Click in cell C12 and type '=VLOOKUP'
On the formula bar click on the = sign.
As soon as you have clicked on the '=' sign the following dialog box appears.
If the dialog box has covered the data just left click near the top of the box and drag it out of the way so you can once again see the data.
The cursor will be flashing in the Lookup_value box.
Now, because we are going to use cell B12 as the data entry box for the name of the person who's number we are looking up we will enter B12. This can be typed in or alternatively you can use your mouse and click on cell B12.
Click in the Table_a"
Along with SUM and SUMIF, VLOOKUP is one of the most used Excel functions.
The VLOOKUP function works by searching down the leftmost column of your data and then reads across the row to find the value in the column you specify.
Clear as mud isn't it?
If you think of the process as similar to that which you would adopt when searching for a phone number in a directory, you won't go far wrong.
In fact, let's use the phone directory scenario to illustrate the Excel VLOOKUP function in action.
Below is a list of names and their corresponding phone numbers. We would like to be able to type in a name and have the phone number automatically returned.
For this particular example I am going to use two cells, one for inputting the name of the person who's number I want, and the second cell will be where I enter the VLOOKUP function which will return the phone number.
Click in cell B12 and type 'David Beckham'. (without the quotes)
Click in cell C12 and type '=VLOOKUP'
On the formula bar click on the = sign.
As soon as you have clicked on the '=' sign the following dialog box appears.
If the dialog box has covered the data just left click near the top of the box and drag it out of the way so you can once again see the data.
The cursor will be flashing in the Lookup_value box.
Now, because we are going to use cell B12 as the data entry box for the name of the person who's number we are looking up we will enter B12. This can be typed in or alternatively you can use your mouse and click on cell B12.
Click in the Table_a"
Microsoft Excel Tutorials from Excel Online Training
Microsoft Excel Tutorials from Excel Online Training: "These free Microsoft Excel tutorials will rapidly transform you into a powerhouse Excel user, ready to take on the toughest of spreadsheets and claim the crown of workplace Excel Guru.
These Microsoft Excel tutorials have been categorized as either Beginner Excel tutorials, Intermediate Excel tutorials or Advanced Excel tutorials.
This is more for ease of navigation around the tutorials than any indication to the ability level needed.
As you will discover, the Excel tutorials will lead you step-by-step, giving precise detail of where to click , what to write etc.
Don't worry, we will have you whizzing round Excel like a pro in no time.:)
If you don't find the answers to your questions here, please drop me a note and I will write an Excel tutorial just for you."
These Microsoft Excel tutorials have been categorized as either Beginner Excel tutorials, Intermediate Excel tutorials or Advanced Excel tutorials.
This is more for ease of navigation around the tutorials than any indication to the ability level needed.
As you will discover, the Excel tutorials will lead you step-by-step, giving precise detail of where to click , what to write etc.
Don't worry, we will have you whizzing round Excel like a pro in no time.:)
If you don't find the answers to your questions here, please drop me a note and I will write an Excel tutorial just for you."
Excel Online Training Sitemap
Excel Online Training Sitemap: "
Microsoft Excel Tutorials - Excel Tutorials for all levels of ability from Excel beginners to Excel power users.
Excel Basics: Getting Started - Quick tutorial on the different ways to launch Microsoft Excel.
Calculating a Weighted Average in Excel - Calculating a weighted average in Excel is simple when you use the SUMPRODUCT function.
Microsoft Excel Formula and Functions - Discover how Excel functions work, which one to use to generate the result you want, and how to combine Excel functions to produce incredible results.
Excel Logic Functions - Excel logic functions give Excel the ability to think... Sort of. Logic functions are used when a decision has to be made, usually when we are comparing our data against several different possibilities.
Excel IF Function - This function enables you to perform a calculation only IF a certain condition is true, and a completely different calculation if that condition is false.
Excel AND Function - The AND function can contain up to 30 separate arguments and if ALL of those arguments are TRUE, the AND function returns TRUE, but if ANY of those arguments are FALSE, the AND function returns
FALSE.
Excel OR Function - This Excel function is similar in operation to the Excel AND function and returns a logical value, either TRUE or FALSE depending on the logical value of each of its arguments.
Excel Text Functions - Excel text functions are used, surprise surprise, on text that is within your spreadsheets.
Excel LEN Function - Basically what the LEN function does is counts how many characters there are in a text string.
Excel LEFT Function - Use the Excel LEFT function to extract a specific number of characters from the LEFT, or beginning, of a text string.
Excel RIGHT Function - Use the Excel RIGHT function to extract a specif"
Microsoft Excel Tutorials - Excel Tutorials for all levels of ability from Excel beginners to Excel power users.
Excel Basics: Getting Started - Quick tutorial on the different ways to launch Microsoft Excel.
Calculating a Weighted Average in Excel - Calculating a weighted average in Excel is simple when you use the SUMPRODUCT function.
Microsoft Excel Formula and Functions - Discover how Excel functions work, which one to use to generate the result you want, and how to combine Excel functions to produce incredible results.
Excel Logic Functions - Excel logic functions give Excel the ability to think... Sort of. Logic functions are used when a decision has to be made, usually when we are comparing our data against several different possibilities.
Excel IF Function - This function enables you to perform a calculation only IF a certain condition is true, and a completely different calculation if that condition is false.
Excel AND Function - The AND function can contain up to 30 separate arguments and if ALL of those arguments are TRUE, the AND function returns TRUE, but if ANY of those arguments are FALSE, the AND function returns
FALSE.
Excel OR Function - This Excel function is similar in operation to the Excel AND function and returns a logical value, either TRUE or FALSE depending on the logical value of each of its arguments.
Excel Text Functions - Excel text functions are used, surprise surprise, on text that is within your spreadsheets.
Excel LEN Function - Basically what the LEN function does is counts how many characters there are in a text string.
Excel LEFT Function - Use the Excel LEFT function to extract a specific number of characters from the LEFT, or beginning, of a text string.
Excel RIGHT Function - Use the Excel RIGHT function to extract a specif"
Learn Excel Online with Tutorials from Excel Online Training
Learn Excel Online with Tutorials from Excel Online Training: "How many times have you been blankly staring at your Excel spreadsheet, scratching your head, thinking 'I wonder if Excel can do this?'
Or, even more likely 'Why has Excel just done that?'
Excel Online Training will give you the answers you're searching for.
Excel Online Training will deliver to you detailed, step-by-step instructions, numerous screenshots and, where possible,Flash movie demonstrations that will offer the clearest no nonsense Microsoft Excel Training you have ever had.
Imagine finally mastering Excel. You're no longer driven to despair by this darned spreadsheet that apparently has a mind of its own.
Your confidence increases, you become more efficient in your work, your colleagues start to come to you with their Excel queries.
Your boss even notices how quickly and accurately you deal with the work that is thrown at you. You're offered a promotion, a pay rise and a corner office...
Ok, so I got a bit carried away. There's no doubt, however, that when you Learn Excel you become much more attractive in the job market.
Whatever your reasons for undertaking Excel training, whether it is to improve your job prospects, analyze your business or just take control of the household budget, I am here to help you."
Or, even more likely 'Why has Excel just done that?'
Excel Online Training will give you the answers you're searching for.
Excel Online Training will deliver to you detailed, step-by-step instructions, numerous screenshots and, where possible,Flash movie demonstrations that will offer the clearest no nonsense Microsoft Excel Training you have ever had.
Imagine finally mastering Excel. You're no longer driven to despair by this darned spreadsheet that apparently has a mind of its own.
Your confidence increases, you become more efficient in your work, your colleagues start to come to you with their Excel queries.
Your boss even notices how quickly and accurately you deal with the work that is thrown at you. You're offered a promotion, a pay rise and a corner office...
Ok, so I got a bit carried away. There's no doubt, however, that when you Learn Excel you become much more attractive in the job market.
Whatever your reasons for undertaking Excel training, whether it is to improve your job prospects, analyze your business or just take control of the household budget, I am here to help you."
