Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

If NA return 0 if populated return 1

Posted on 2014-11-28
4
78 Views
Last Modified: 2014-11-28
Column P contains the results of a Vlookup.

How can I change the formula so that the cells display a value of 1 when the Vlookup returns a name and 0 when the lookup returns N/A?

Thanks
Rob
Supplier-Partnership-Criteria.xlsx
0
Comment
Question by:robmarr700
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40470296
One way:

=IF(ISNA(MATCH(A5,'C:\Angla Elvin\Supplier and Member Figures\[Oct14_Final_Members.xlsm]Supplier Preference1_PT'!$A:$A,0)),0,1)
0
 

Author Comment

by:robmarr700
ID: 40470301
That's great, good job!
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40470507
marginally simpler, perhaps?
=IF(ISTEXT(VLOOKUP(A5,'C:\Angla Elvin\Supplier and Member Figures\[Oct14_Final_Members.xlsm]Supplier Preference1_PT'!$A:$A,1,FALSE)),1,0)

Not sure if the Match adds very much in the option above, but I may be missing something?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40470585
How is that simpler out of interest?

MATCH is faster than VLOOKUP. ;)
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question