Excel 2010/2013
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 

News      User Comments     History     Notify Me

1/1/2007
Microsoft Excel 202
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
In this class, we will be learning about Named Cells and Named Ranges, Nested IF Functions, and the very powerful VLOOKUP and HLOOKUP functions. These new functions will allow us to look up a value in a subtable.

Permanent Link
Course Link: Microsoft Excel 202
Keywords: excel
Post Reply

Comment from  Tom @ 6/9/2011
which lesson do I need to buy to tell me how to do this if funtion with dates;
example:  if a child is born between 09/01/06 and 09/01/07, then he is 4 as of 09/01/11 and can be in kindergarten, if not, then not 4.
Hope this make sense, thanks


Reply from Richard Rost:

I teach you how to calculate someone's age exactly in Excel 2010 Expert Level 2. You can then use the IF function to perform any if/then calculations on it. IF is covered in Level 3.
Show Just This Thread        Post Reply
Comment from  Sandy @ 8/26/2010
I have a question concerning Vlookup when populating alpha data from one spreadsheet to another.  Normally I work with very large spreadsheets.  I've found that if the field is not unique, vlookup returns the first value it finds for the lookup field.  Is there a work around so it will also look at secondary information or ???  Possibly a vlookup in conjunction with hlookup?
Show Just This Thread        Post Reply
Comment from  UMESH @ 2/22/2010
VERY VERY INFORMATIVE.


Reply from Richard Rost:

Thank you.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 1/3/2010
Normally VLOOKUP looks for the "closest numeric match" - like my example with the test grades. If you want to find an EXACT MATCH there is a fourth optional parameter you can use to specify that.
Show Just This Thread        Post Reply
Comment from Lynne @ 1/3/2010
Hi Richard
Do V & Hlookup only work for numeric?
I have S/S1 with supplers item nunber(mixed alpha/num) & sales $  & need to match to S/S2 that has suppliers & my item number(alpha/numeric). I need to bring my item number back to S/S1. I cant get Vlookup to work???
thanks
Show Just This Thread        Post Reply
Comment from Richard Rost @ 12/14/2009
Emma, I've never had any problems with Excel 202 losing audio. This class is over 4 years old now, and nobody else has complained about it. Perhaps it was a buffering issue on your PC. I would recommend rebooting and trying again.

If you're having problems understanding something in one of the lessons, just post your questions here and I'll be happy to answer them. Remember, the lessons make much more sense the SECOND time through. Watch the video ONCE through without doing anything. Then watch it a second time and follow along with my examples.
Show Just This Thread        Post Reply
Comment from Emma Lara @ 12/13/2009
Hi richard,  I lost audio  during some parts of the video.I cheked my computer and it wasn" on mute, what do you think happened?

The lessons looked easy,  but I I don't know if that's still the case when I do it on my own.  Thank you.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 1/20/2009
Robert, I'm sure there IS a maximum, although without researching it, I don't know what it is offhand. I know that I have personally used lookup tables in Excel that were 20 or so columns wide. Generally, VLOOKUP is used for small lists. Anything bigger really should be done in Access.
Show Just This Thread        Post Reply
Comment from Robert Samagalsky @ 1/11/2009
Is there a maximum number of columns allowed for the columns allowed in the lookup table itself?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 3/13/2008
Carla, I'd love to help you, but without seeing your spreadsheet and knowing more about what you want to accomplish, it's impossible for me to tell you what direction to go in. You can submit your spreadsheet with detailed instructions to me on my www.599cd.com/TECHHELP page.
Show Just This Thread        Post Reply
Comment from Carla Gamble @ 3/11/2008
OK, new to VLOOKUP, not a real quick study here,been reading up on VLOOKUP and understand the concept. I can't apply it to my spreadsheet at work.
I have a excel shortage  report. I need to take the report the vendor returns back to me and put updated information on my orginal shortage  report. IE: parts in stk, qty, pricing, etc. HELP  
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 3+8:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

You may want to read these articles from the 599CD News:

5/4/2014Microsoft Access Expert 20
3/5/2013Excel Tip: Price Markups
2/3/2013Signed Copies of my Excel Book
9/27/2012Excel Expert 10 Handbook Ready
9/18/2012Two New Excel Tips: Date/Time Differences
6/20/2012Excel Tip: Sum Cells Between Two Values
6/16/2012New Excel Tip: Paste Link
3/26/2012Excel Tip: 2D Matrix Lookups
11/18/2011Dashboards in Excel
10/6/2011Excel Expert 11 On Hold
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP