# 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