Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

If NA return 0 if populated return 1

Posted on 2014-11-28
4
Medium Priority
?
88 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

916 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