UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> how to do LOOKUP without getting "#N/A" results    
 
   
pcaling
post Jul 24 2005, 09:52 AM
Post #1

UtterAccess Member
Posts: 47
Hello,

I have two worksheets, 1 tab and 2nd tab. The 1st tab contains the list of zip codes(A column) and 2nd tab has the list of employees with their zip codes(zip code is in B column). I added a second column(B col) to the 1 tab with a value of '1' to all zip codes. On the 2nd tab, I have a formula "If( vlookup(b1,1st tab,2, false)=1, "eligible", "not eligible"). The problem is when a zip code is not listed in the 1st tab, it is giving me a result of "#N/A", which is right since the zip code from the 2nd tab is not listed in the 1st tab.

What I want or trying to do is, if zip code from the 2nd tab is not listed in the 1st tab, it will display in the 2nd tab across the zip code "not eligible". If listed will display'eligible".

Thanks in advance,

Pedro
Go to the top of the page
 
+
dannyseager
post Jul 24 2005, 09:56 AM
Post #2

UtterAccess VIP
Posts: 13,031
From: Leicester, UK
try

=If(IsNA(vlookup(b1,1st tab,2, false)),"not eligible","eligible")
Go to the top of the page
 
+
pcaling
post Jul 25 2005, 08:57 AM
Post #3

UtterAccess Member
Posts: 47
Thanks Danny for the help! Now my spreadsheet is working properly...

Pedro
Go to the top of the page
 
+
dannyseager
post Jul 28 2005, 09:10 PM
Post #4

UtterAccess VIP
Posts: 13,031
From: Leicester, UK
glad to help
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 06:49 PM