MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Nesting Vlookups in if..then statements.


Posted by Ben Houck on April 12, 2001 5:24 PM

I was wondering if someone could provide me with guidence on the statment below:

=IF(VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,2,FALSE)=1,("If(VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,3,FALSE)='PUR')"),("(VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,5,FALSE),""0"))

I'm trying to get the formula to return the last Vlookup statement in the series if the other two are true. The false values at the end should all be zero. I understand that the way it is written no views the third vlookup as the true value for the first if statement but I can't get it to return the value only the verbatim text.

I've also tried this conbination with no luck:
=IF(VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,2,FALSE)=1,("If(VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,3,FALSE)='PUR')"),(VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,5,FALSE)))

Any help would be greatly appreciated.

Thanks
Ben


Posted by Dave Hawley on April 12, 2001 5:40 PM

Hi Ben

If I have understood you, this should work:

=IF(AND(VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,2,FALSE)=1,VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,3,FALSE)="PUR"),VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,5,FALSE),0)


Dave

OzGrid Business Applications

Posted by BenHouck on April 13, 2001 4:49 AM

Perfect. Thanks again Dave!