Need help with Nested IF function

You don't have any data in columns S or I in the data sheet, so please re-state conditions 2 and 4.

Pete

Hi Pete ,
sorry.hey I meant to say column E not S
Thanks

Waheed

Hi Guys,

I have revised the conditions in the file with the correct columns. Thanks

Waheed

Hi there,

i actually dont need the VBA script.... all i need a proper and right formula to get the data. Thanks

waheed

Originally Posted by xladept

... Pete can probably deliver a formula for you:

Such faith !!

This is close, but is not quite matching all your suggested values. I put this formula in F2:

=IFERROR(VLOOKUP(A2,'Data to lookup the dates'!A:E,IF(C2="Remodel",IF(AND(B2="Owned",VLOOKUP(A2,'Data to lookup the dates'!A:E,5,0)=0),2,5),IF(OR(B2="Top Lease",B2="Ground Lease"),IF(VLOOKUP(A2,'Data to lookup the dates'!A:E,3,0)=0,4,3))),0),"Fix")

and copied down. It's not quite working in all cases, but I'm posting it now in case I don't get chance to come back to it, as I have to catch a plane at 7:00 am in the morning, so I must go and do some packing and then an early night.

Hope this helps.

Pete

Thanks Pete. The formula is not working on some of the conditions and i tried to tweak it in so many ways but it does not seem to work. I will keep trying still. Thanks

Waheed

Here's an improvement. You didn't quite state your conditions succinctly, as the "Owned" condition seemed to apply only to "Remodel" being in C2 according to your text. There were 4 entries for Owner without Remodel which caused some problems with the other formula, so this formula (again in F2) corrects for that and now there is only one condition not being met (the last one):

=IFERROR(VLOOKUP(A17,'Data to lookup the dates'!A:E,IF(C17="Remodel",IF(AND(B17="Owned",VLOOKUP(A17,'Data to lookup the dates'!A:E,5,0)=0),2,5),IF(B17="Owned",2,IF(OR(B17="Top Lease",B17="Ground Lease"),IF(VLOOKUP(A17,'Data to lookup the dates'!A:E,3,0)=0,4,3)))),0),"Fix")

The failing condition is for a Remodel and a Top Lease, but you haven't defined a condition for Top Lease with Remodel - I suppose you want this to be treated similar to Owned and Remodel with an empty date, in which case the formula will become:

=IFERROR(VLOOKUP(A2,'Data to lookup the dates'!A:E,IF(C2="Remodel",IF(AND(B2="Owned",VLOOKUP(A2,'Data to lookup the dates'!A:E,5,0)=0),2,IF(AND(RIGHT(B2,5)="Lease",VLOOKUP(A2,'Data to lookup the dates'!A:E,5,0)=0),3,5)),IF(B2="Owned",2,IF(OR(B2="Top Lease",B2="Ground Lease"),IF(VLOOKUP(A2,'Data to lookup the dates'!A:E,3,0)=0,4,3)))),0),"Fix")

and this does indeed give you the same results as you expected.

I couldn't leave this hanging before going away, so I shall get some shut-eye now.

Hope this helps.

Pete