x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 217

Hi Experts excel 2007

I have the following of formula in cell b2
If (A2="", if (c2="","", c2), VLOOKUP (a2,'sheet2'! \$ a \$2:\$b \$20,2, False))

Which is fine...I need to add the following step...if g2 date is less then todays date then replace c2 with complete,  if g2 has no date then above formula.
0
route217
• 9
• 7
1 Solution

=IF(G2<TODAY(),C2,If (A2="", if (c2="","", c2), VLOOKUP (a2,'sheet2'! \$ a \$2:\$b \$20,2, False)))
0

Author Commented:
Excellent feedback.
0

Author Commented:
Hi itjockey

The formula errors wlhen g2 is blank...it should return the value in c2..otherwise prefect.
0

``````=IF(OR(G2="",G2<TODAY()),C2,IF(A2="",IF(C2="","",C2),VLOOKUP(A2,sheet2!\$A\$2:\$B\$20,2,FALSE)))
``````
0

Mr.route217

i dint write your formula in Excel sheet as there are many references of other sheet which i dnt have. So above formula i had written in Note pad so it may ask for auto complete. just click yes. Or provide me sample file.

Thanks
0

Author Commented:
Hold on...could be my fault

If g2 is blank then f2, otherwise if g2 has date then h2..then rest of the formula. ..

Sorry
0

Author Commented:
0

Try this
``````=IF(IF(G2="",F2,IF(CELL("Format",G2)="D1",H2)),IF(A2="",IF(C2="","",C2),VLOOKUP(A2,Sheet2!\$A\$2:\$B\$20,2,FALSE)))
``````
0

Author Commented:
Itjockey

Thanks but getting #value error
0

ok don't provide me sample WB but list down your criteria for whole formula.

like...
1.If G2 is blank then F2,
2.if G2 has date then H2
0

Author Commented:

Based on this formula =IF(OR(G2="",G2<TODAY()),C2,IF(A2="",IF(C2="","",C2),VLOOKUP(A2,sheet2!\$A\$2:\$B\$20,2,FALSE)))

amend kindly do if g2 is blank the return h2..if g2 has a date then as normal as above.
0

There are two parts of formula tell me which part execute when as formula is conflicting with criteria.

Part 1
If (A2="", if (c2="","", c2), VLOOKUP (a2,'sheet2'! \$ a \$2:\$b \$20,2, False))

Part 2
=IF(G2="",F2,IF(CELL("Format",G2)="D1",H2))

Thanks
0

Author Commented:
Hi itjockey. ..
Firstly thanks for all the excellent feedback...I have to post a new question..clearly with better wording.
0

ok No problem but after you find solution pls post here formula or link for my understanding.

Thanks
0

Author Commented:
And once again appreciate the patience. ..
0

Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.