Results 1 to 3 of 3
Thread: Reference Vlookup Result

20090716, 09:06 #1
 Join Date
 Jun 2002
 Location
 Michigan City, Indiana, USA
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20090716, 11:05 #2
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='jojames' post='784920' date='16Jul2009 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)))))Regards
Don

20090716, 14:58 #3
 Join Date
 Jun 2002
 Location
 Michigan City, Indiana, USA
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='wdwells' post='784937' date='16Jul2009 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