Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
programming4us
Windows 7

Microsoft Excel 2010 : Building More Powerful Worksheets - Using Lookup and Reference Functions & Using Text Functions

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/31/2011 9:16:55 AM

Using Lookup and Reference Functions

You can use lookup and reference functions in Excel to easily retrieve information from a data list. The lookup functions (VLOOKUP and HLOOKUP) allow you to search for and insert a value in a cell that is stored in another place in the worksheet. The HLOOKUP function looks in rows (a horizontal lookup) and the VLOOKUP function looks in columns (a vertical lookup). Each function uses four arguments (pieces of data) as shown in the following definition: =VLOOKUP (lookup_value, table_array, col_index_num, range_lookup). The VLOOKUP function finds a value in the left-most column of a named range and returns the value from the specified cell to the right of the cell with the found value, while the HLOOKUP function does the same to rows. In the example, =VLOOKUP(12,Salary,2,TRUE), the function looks for the value 12 in the named range Salary and finds the closest (next lower) value, and returns the value in column 2 of the same row and places the value in the active cell. In the example, =HLOOKUP (“Years”,Salary,4,FALSE), the function looks for the value “Years” in the named range Salary and finds the exact text string value, and then returns the value in row 4 of the column.

Use the VLOOKUP Function

Create a data range in which the left-most column contains a unique value in each row.

Click the cell where you want to place the function.

Type =VLOOKUP(value, named range, column, TRUE or FALSE), and then press Enter.



Or click the Formulas tab, click the Look & Reference button, click VLOOKUP, specify the function arguments, and then click OK.

Use the HLOOKUP Function

Create a data range in which the uppermost row contains a unique value in each row.

Click the cell where you want to place the function.

Type =HLOOKUP(value, named range, row, TRUE or FALSE), and then press Enter.



Or click the Formulas tab, click the Look & Reference button, click HLOOKUP, specify the function arguments, and then click OK.

Lookup Function Arguments

ArgumentDescription
lookup_valueThe value found in the row or the column of the named range. You can use a value, cell reference or a text string (enclosed in quotation marks).
table_arrayThe named range of information in which Excel looks up data.
col_index_numThe numeric position of the column in the named range (counting from the left) for the value to be returned (use only for VLOOKUP).
row_index_numThe numeric position of the row in the named range (counting from the top) for the value to be returned (use only for HLOOKUP).
range_lookupThe value returned when the function is to find the nearest value (TRUE) or an exact match (FALSE) for the lookup_value. The default value is TRUE.

Using Text Functions

You can use text functions to help you work with text in a workbook. If you need to count the number of characters in a cell or the number of occurrences of a specific text string in a cell, you can use the LEN and SUBSTITUTE functions. If you want to narrow the count to only upper or lower case text, you can use the UPPER and LOWER functions. If you need to capitalize a list of names or titles, you can use the PROPER function. The function capitalizes the first letter in a text string and converts all other letters to lowercase.

Use Text Functions

Create a data range in which the left-most column contains a unique value in each row.

Click the cell where you want to place the function.

Type = (equal sign), type a text function, specify the argument for the selected function, and then press Enter.

Some examples include:

  • =LEFT(A4,FIND(“ ”,A4)-1)

  • =RIGHT(A4,LEN(A4-FIND(“*”,SUBSTITUTE(A4,” “,”*”,LEN(A4)- LEN(SUBSTITUTE(A4,” “,””)))))

  • =UPPER(A4)

  • =LOWER(A4)

  • =PROPER(A4)

Or click the Formulas tab, click the Text button, click a function, specify the function arguments, and then click OK.


You can use wildcard characters in a criteria. A question mark (?) matches any single character. An asterisk (*) matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Other -----------------
- Microsoft Excel 2010 : Building More Powerful Worksheets - Using Solver
- Microsoft Visio 2010 : Editing Shape Data
- Microsoft Visio 2010 : Viewing Shape Data
- Managing Disks : Understanding RAID & Using Disk Maintenance Tools
- Managing Disks : Managing Data Encryption with EFS
- Microsoft Excel 2010 : Building More Powerful Worksheets - Creating Scenarios
- Microsoft Excel 2010 : Building More Powerful Worksheets - Looking at Alternatives with Data Tables
- Microsoft Excel 2010 : Building More Powerful Worksheets - Using Data Analysis Tools & Using the Euro Conversion
- Microsoft Visio 2010 : Setting Theme Options
- Microsoft Visio 2010 : Customizing Themes
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer