Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-12-2011, 08:37 AM
Karen615 Karen615 is offline Vlookup Windows 7 64bit Vlookup Office 2010 64bit
Competent Performer
Vlookup
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
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 Vlookup Windows 7 32bit Vlookup Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
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 Vlookup Windows 7 64bit Vlookup Office 2010 64bit
Competent Performer
Vlookup
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
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 Vlookup Windows 7 32bit Vlookup Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
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 Vlookup Windows 7 64bit Vlookup Office 2010 64bit
Competent Performer
Vlookup
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
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
Vlookup 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft