Reference Vlookup Result

[jojames]

I have a spreadsheet that contains mostly Vlookup formulas which gather data from other worksheets to put on the main worksheet. On some cells I want to use the result of the vlookup in a formula, but it doesn't seem to be working. For example, I have the following formula:

=IF(AND((ISblank(AC3)),(ISblank(W3))),"88/88/8888",(IF(NOT(ISBLANK(AC3)),AC3,(VLOOKUP(G3,'SA Info'!$A$1:$J$991,10,FALSE)))))

AC3 and W3 both contain vlookup formulas, so it is not reading the cell as blank. The result of the vlookup in these formulas is blank. The formula in AC3 is =IF(ISNA(VLOOKUP(G3,Graduate!$A$1:$I$22,8,FALSE)), "",VLOOKUP(G3,Graduate!$A$1:$I$22,8,FALSE)) and in W3 is =IF(ISNA(VLOOKUP(G3,'SA Info'!$A$1:$H$991,8,FALSE)),"",(IF(OR(LEFT(VLOOKUP (G3,'SA Info'!$A$1:$H$991,8,FALSE),5)="trans",LEFT(VLOOKUP (G3,'SA Info'!$A$1:$H$991,8,FALSE),4)="acad",LEFT(VLOOKUP( G3,'SA Info'!$A$1:$H$991,8,FALSE),4)="with"),VLOOKUP(G3,' SA Info'!$A$1:$H$991,8,FALSE),"")))

I tried replacing ISBLANK(AC3) and ISBLANK(W3) in the first formula above with ISNA, but that didn't work either. It doesn't read it as blank because there is a formula in the cell (I think). Do I have to replace ISBLANK(AC3) with the vlookup formula that is in AC3? I was hoping to avoid that since it gets very confusing.

Thanks for any help you can provide.

Jodi

[wdwells]

[quote name='jojames' post='784920' date='16-Jul-2009 10:06']I have a spreadsheet that contains mostly Vlookup formulas which gather data from other worksheets to put on the main worksheet. On some cells I want to use the result of the vlookup in a formula, but it doesn't seem to be working. For example, I have the following formula:

=IF(AND((ISblank(AC3)),(ISblank(W3))),"88/88/8888",(IF(NOT(ISBLANK(AC3)),AC3,(VLOOKUP(G3,'SA Info'!$A$1:$J$991,10,FALSE)))))

AC3 and W3 both contain vlookup formulas, so it is not reading the cell as blank. The result of the vlookup in these formulas is blank. The formula in AC3 is =IF(ISNA(VLOOKUP(G3,Graduate!$A$1:$I$22,8,FALSE)), "",VLOOKUP(G3,Graduate!$A$1:$I$22,8,FALSE)) and in W3 is =IF(ISNA(VLOOKUP(G3,'SA Info'!$A$1:$H$991,8,FALSE)),"",(IF(OR(LEFT(VLOOKUP (G3,'SA Info'!$A$1:$H$991,8,FALSE),5)="trans",LEFT(VLOOKUP (G3,'SA Info'!$A$1:$H$991,8,FALSE),4)="acad",LEFT(VLOOKUP( G3,'SA Info'!$A$1:$H$991,8,FALSE),4)="with"),VLOOKUP(G3,' SA Info'!$A$1:$H$991,8,FALSE),"")))

I tried replacing ISBLANK(AC3) and ISBLANK(W3) in the first formula above with ISNA, but that didn't work either. It doesn't read it as blank because there is a formula in the cell (I think). Do I have to replace ISBLANK(AC3) with the vlookup formula that is in AC3? I was hoping to avoid that since it gets very confusing.

Thanks for any help you can provide.

Jodi[/quote]
Try replacing ISBLANK(Ref) with Ref = ""
=IF(AND((AC3=""),(W3="")),"88/88/8888",(IF(NOT(AC3=""),AC3,(VLOOKUP(G3,'SA Info'!$A$1:$J$991,10,FALSE)))))

[jojames]

[quote name='wdwells' post='784937' date='16-Jul-2009 16:05']Try replacing ISBLANK(Ref) with Ref = ""
=IF(AND((AC3=""),(W3="")),"88/88/8888",(IF(NOT(AC3=""),AC3,(VLOOKUP(G3,'SA Info'!$A$1:$J$991,10,FALSE)))))[/quote]


That worked! Thanks!

Jodi