Excel Advisor Increasing the size of task bar in separate

A vertical lookup is an extremely useful – but little-used – feature of Excel. It allows you to link data arranged in a table to data in your spreadsheet by matching a field contained in both the table and the spreadsheet

A vertical lookup is an extremely useful – but little-used – feature of Excel. It allows you to link data arranged in a table to data in your spreadsheet by matching a field contained in both the table and the spreadsheet – for example: an i.d. number, a last name, a zipcode, etc.

For example, say you have a spreadsheet that lists your employees and their home addresses, and another spreadsheet that lists your employees and their work departments. Suppose you want to put that information together – have one spreadsheet containing both the home address AND the department information. Vlookup will use a field that both spreadsheets have – in this case the employee name – and look up the information (the department) in the second spreadsheet and display that information (the department) in the first spreadsheet.

Here’s how to do it:

Copy the second spreadsheet onto a worksheet in the first spreadsheet, so that the information is all in the one Excel file, but on different worksheets (tabs). Sort the data on the second spreadsheet in ascending order, on the employee name field. Now you want to look up each employee’s department and put it on the spreadsheet with the employee’s address.

On the first worksheet (with the employee name and address), type in a header for Department.

In the cell under the header, type in the formula for vlookup: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) Look_up value is the cell address of the field the two worksheets have in common – in this case, the employee name.

Type a comma (very important!)

Table_array is the table that contains the department name for the employees – in this example, you’ve placed it on another worksheet (tab) in this file. To enter this information into the formula, go to the worksheet the table is on and select the entire table (you can hold the mouse button down and run the cursor over the table: Excel will enter it automatically). Turn this address into an absolute address by typing a $ in front of the letter and the number of the cell address – for example, A1:B156 would become $A$1:$B$156.

Type a comma (very important!)

Col_index_num is the number of the column in the table that contains the information you are looking for. In this case it’s “2″ because the department information is in the second column of the table.

Type a comma (very important!)

Type FALSE. This tells Excel to return a department from the table only if the name is an exact match. If you type TRUE, if there is not an exact match, Excel will return the closest one. Type a close parenthesis ) to end the formula, and press Enter.

You will be returned to the first worksheet. The cell containing the vlookup formula will now display the department for the employee. Copy the formula down for the rest of the employees. You’ve now successfully combined the information from the two separate sheets onto one sheet, linked by the employee’s name.

For illustrated step by step instructions, see the Articles page at the Next Level Skills website (see below).

Fran Christ, certified Microsoft Trainer, is the author of Question-and-Answer columns at the website http://www.NextLevelSkills.net You can submit your questions for free help with Excel, Word, or Outlook. Follow us at Twitter for new answers to questions on Excel, Word, Outlook: http://twitter.com/nextlevelskills

Article Source: http://EzineArticles.com/?expert=Frances_Christ

3_separate.htm

Leave a Reply