Solved

If NA return 0 if populated return 1

Posted on 2014-11-28
4
83 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
[X]
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
  • 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

623 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