How to write nested if statement based on condition from other table

[myou]

 I have two tables 

OppTable

Opp id	statecodename	Actual close date
1	lost	5-7-2016
2	open	5-9-2015
3	won	10-5-2016

updatesstagechange Table

Custom_opportunityid	stage	createdon	nextstagestartdate	Oppstagerank
1	prospect	1-1-2016	2-1-2016	1
1	qualify	2-1-2016	5-7-2016(Actual close date from Opptable)	2
2	prospect	3-1-2016	4-1-2016	1
2	qualify	4-1-2016	5-1-2016	2
2	develop	5-1-2016	NOW DAte	3
3	Prospect	6-1-2016	7-1-2016	1
3	Qualify	7-1-2016	8-1-2016	2
3	develop	8-1-2016	10-5-2016(Actual close date from Opptable)	3

How to do it , if I want to make a condition if opportunity is lost or won, then the  next blank record in  updatesstagechange Table will take the actual close date from OppTable, else if open it takes today date

I wrote this column but its putting today date for all close, open and lost

NextStageStart date = IF(ISBLANK(LOOKUPVALUE(updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1)),NOW(),LOOKUPVALUE(Updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1))

OppStageRank = COUNTROWS( FILTER(

                                    updatesstagechange,

                                                     updatesstagechange[custom_opportunityid]<>""&&

                                              updatesstagechange[custom_opportunityid]=EARLIER(updatesstagechange[custom_opportunityid])&&

                                                     updatesstagechange[createdon]<EARLIER(updatesstagechange[createdon])

                                                          )

                                                  )+1

[v-sihou-msft]

@myou

In your expression:

LOOKUPVALUE(updatesstagechange[createdon],Updatesstagechange[Custom_opportunityid],Updatesstagechange[Custom_opportunityid],Updatesstagechange[OppStageRank],Updatesstagechange[OppStageRank]+1)

The current row "Updatesstagechange[Custom_opportunityid]" can never equal to "Updatesstagechange[Custom_opportunityid]+1". So your "NextStageStart date" will always return NOW().

Based on your description, you want to show different date based on Opp ID. Your expression should be like below:

NextStageStart date =
IF (
    updatesstagechange[Custom_opportunityid] = 2,
    NOW (),
    LOOKUPVALUE (
        Opp[Actual close date],
        Opp[Opp id], updatesstagechange[Custom_opportunityid]
    )
)

Regards,