Error Messages, VLookup, Practical Tips

1
Error Messages, VLookup, Practical Tips
What use is VLookup? How do you import text
into Excel?
2
VLOOKUP, HLOOKUP Function

• assigns a value to a cell based on a numeric
  value in another cell
• Allows Excel to look up a value in a table and
  return a related value
• for example, Excel can look up your average in
  this class and return your grade

3
More on Vlookup

• Requires three arguments
• the numeric value (or cell) to look up
• the range of the table
• the column number containing the value you want
  to return
• VLOOKUP(X5,AC2AE14,3)

Note column AD is hidden, so we say column 3 in
the formula
4
Vlookup for student with 733 points
Using an approximate match, searches for the
value 733 in column AC, finds the largest value
less than or equal to 733 in column AC which is
720 and then returns the value from column AE in
the same row.
5
VLOOKUP(C18,E13F17,2)
Col.2 of LOOKUP table
LOCK!
Cell to evaluate
Lookup Table is E13F17
Use vlookup.xls spreadsheet
6
String Operations

• Concatenation (putting two words together)
• Operator is
• e.g. B3 C4
• Concatenation can also be done by a function
• e.g. CONCATENATE(B3,C4)
• Operands can be
• character strings Tarzan Jane
• cell addresses containing text values B3 B4

7
Concatenating -- Using
Using the wizard to enter a function

• As in MySQL, you can add a space between each
  word.

8
Adding a space

• Strings always appear in quotes in Excel.
• To add a space, just put it between quotes.
• Remember, there is an ASCII value for a space!
• You can also put a space between the quote and
  the start of Water

9
Providing User Feedback

• Some functions are built into Excel to help
  provide feedback and test content
• They could be helpful to you

10
Examples of useful functions

• ISERROR(B2) equals TRUE
• ISNUMBER(B3) equals FALSE
• Formula view

11
Examples of useful functions

• ISTEXT(B4) equals TRUE
• ISBLANK(B5) equals TRUE
• Formula view

12
How to use these functions

• You want to calculate the average of the range
  C1C4, but you can't be sure that the cells
  contain numbers.
• The formula AVERAGE(C1C4) returns DIV/0!
• if C1C4 does not contain any numbers. Use
• IF(ISERROR(AVERAGE(C1C4)),"No
  Numbers",AVERAGE(C1C4))

13
Importing Data into Excel

• You can import data and not have to retype it.
• Excel can create spreadsheets from Word tables
• You can import and export spreadsheets and
  databases

14
To import a text file
15
(No Transcript)
16
Testing your knowledge

• VLookUp does what?
• ISTEXT returns what?
• Delimiterwhats that?
• Your moment of Zen
• http//youtube.com/watch?vK50l-9RNBo8