I need an excel formula

Posted on 2011-04-27
Last Modified: 2012-05-11
I will try to explain the best way I can. I am currently using this formula:

=(OR(C6="Complete",C6="Complete - Successful"))+0

It tells me that if the value in C6 is either Complete or Complete - Successful to make it  1, or 100%.

I am wanting to also add to it to check to see if a accounting code matches (Column B on both Sheets), if it does, add the value thats in column K. If not, use the "Complete" or "Complete - Succcessul."

See the attached for an example file. I hope I have explained it well, if not, I will respond asap.
Question by:wrt1mea
    LVL 81

    Accepted Solution

    Use this function:

    =IF(ISERROR(MATCH(B2,Survey!B$2:B$1000,0)),(OR(C2="Complete",C2="Complete - Successful")+0),INDEX(Survey!K$2:K$1000,MATCH(B2,Survey!B$2:B$1000,0)))

    LVL 1

    Author Closing Comment

    Works great! Thank you and be on the lookout for future postings.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now