Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-12-2011, 08:37 AM
Karen615 Karen615 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jun 2011
Location: Chicago
Posts: 33
Karen615 is on a distinguished road
Smile Vlookup

Iím using VLOOKUP but not getting the correct results.

Sheet1 (Reference Table)


Column A = License No.
Column B = State

Sheet2
Column A = License No.
I have to pull in the states that correspond to each license number.
Below is my formula. Some return values are correct are some are not. I cannot figure out why this is happening.
=VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2)
Your help is greatly appreciated
Reply With Quote
  #2  
Old 09-12-2011, 10:56 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

Hi Karen,

You also need to specify a fourth argument, which tells it to do an exact look up:

=VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0)

If you omit the fourth argument (it is optional) then VLOOKUP does an approximate match. This can be great when your data is sorted, because the formula calculates more quickly.
Reply With Quote
  #3  
Old 09-12-2011, 12:10 PM
Karen615 Karen615 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jun 2011
Location: Chicago
Posts: 33
Karen615 is on a distinguished road
Default

Thank you!

It worked out great! Using the below example of the formula, what can be done to the formula so it elimates the #N/A error?
Is it possible to replace the #N/A with the words "no match"?

=VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0)

Thank you for your help!
Reply With Quote
  #4  
Old 09-12-2011, 01:24 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

Hi Karen,

Sure, if you are using Excel 2007 or later you can do it like this:

=IFERROR(VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0),"no match")

If you are using Excel 2003 or earlier then you can do it like this:

=IF(ISNA(VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0)),"no match",VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0))
Reply With Quote
  #5  
Old 09-12-2011, 02:30 PM
Karen615 Karen615 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jun 2011
Location: Chicago
Posts: 33
Karen615 is on a distinguished road
Default

Thank you so much! You are da bomb!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Vlookup & IF together thelauncher Excel 5 08-25-2013 11:32 PM
Can i do this with a VLookup? foodstudent Excel 1 01-21-2011 12:34 AM
Using IF & VLOOKUP together junction Excel 7 11-18-2010 05:15 AM
Help with VLOOKUP sakhtar Excel 2 07-24-2010 07:39 PM
vlookup question Tony G Excel 9 03-28-2009 11:16 PM


All times are GMT -7. The time now is 10:07 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft