Solved

amend if formula

Posted on 2014-04-08
5
160 Views
Last Modified: 2014-04-08
Hi Experts excel 2007

I need to amend the following. ..
=IF(C2="Completed","Complete",IF(C2<>"",C2,IF(AND(C2="",A2=""),"",IF(C2="",VLOOKUP(A2,H12:L19,2,FALSE)))))

To if c2 is complete then complete...if a2 has text then vlookup, if A2 is blank then c2..if a2 is blank and c2 is blank then blank. .
0
Comment
Question by:route217
  • 2
  • 2
5 Comments
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 100 total points
ID: 39985564
=IF(C2="Completed","Complete",IF(A2="",C2,VLOOKUP(A2,H12:L19,2,FALSE)))

however if the lookup fails this will return 0 - is that a problem?
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39985566
=IF(C2="Complete","Complete",IF(A2<>"",VLOOKUP(A2,H12:L19,2,FALSE),IF(A2="",C2,IF(AND(A2="",C2=""),""))))
0
 

Author Comment

by:route217
ID: 39985571
Hi expert

What about if a2 is blank and c2 is blank. ..then would the formula return blank?
0
 
LVL 8

Accepted Solution

by:
itjockey earned 400 total points
ID: 39985573
=IF(C2="Complete","Complete",IF(A2<>"",VLOOKUP(A2,H12:L19,2,FALSE),IF(AND(A2="",C2=""),"",IF(A2="",C2))))
0
 

Author Comment

by:route217
ID: 39985582
Thank for the feedback
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

773 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