If, then, vlookup

[akemeny]

Ok... so I've tried doing this every which way I can think of. I have
multiple spreadsheets that I need to have looked at to verify that the
account numbers do not show on any other spreadsheet. Most of the
spreadsheets are in seperate workbooks. Here are two of the more detailed
functions I've tried:

1. =IF(ISERROR(VLOOKUP(Q2,'[CERTS, DRGS & ADRS.xls]Old
CERTs'!$C:$C,1,FALSE)),"",IF(VLOOKUP(Q2,'[CERTS, DRGS & ADRS.xls]Old
CERTs'!$C:$C,1,FALSE),"yes",IF(ISERROR(VLOOKUP(Q2,'[CERTS, DRGS &
ADRS.xls]A-K'!$O:$O,1,FALSE)),"",IF(VLOOKUP(Q2,'[CERTS, DRGS &
ADRS.xls]A-K'!$O:$O,1,FALSE),"yes",IF(ISERROR(VLOOKUP(Q2,'[CERTS, DRGS &
ADRS.xls]L-Z'!$O:$O,1,FALSE)),"",IF(VLOOKUP(Q2,'[CERTS, DRGS &
ADRS.xls]L-Z'!$O:$O,1,FALSE),"yes",""))))))

2. =IF(VLOOKUP(Q2,'[CERTS, DRGS & ADRS.xls]Old
CERTs'!$C:$C,1,FALSE),"yes",IF(ISERROR(VLOOKUP(Q2,'[CERTS, DRGS &
ADRS.xls]Old CERTs'!$C:$C,1,FALSE)),"",IF(VLOOKUP(Q2,'[CERTS, DRGS &
ADRS.xls]A-K'!$O:$O,1,FALSE),"yes",IF(ISERROR(VLOOKUP(Q2,'[CERTS, DRGS &
ADRS.xls]A-K'!$O:$O,1,FALSE)),"",IF(VLOOKUP(Q2,'[CERTS, DRGS &
ADRS.xls]L-Z'!$O:$O,1,FALSE),"yes",IF(ISERROR(VLOOKUP(Q2,'[CERTS, DRGS &
ADRS.xls]L-Z'!$O:$O,1,FALSE)),"",""))))))

Any help would be greatly appreciated.

 

[Squeaky]

In example one,
in your last three instances of "[CERTS, DRGS & ADRS.xls]" there is a
carriage return in the formula after the "&". Remove the carriage return and
see if it will work. You can verify what I am saying by copying the first
[CERTS, DRGS & ADRS.xls] into a cell by itself, then take the last [CERTS,
DRGS & ADRS.xls] in the formula and paste it to the cell below it for
comparison. They will show different. I suspect there is a similar problem
with the other formulas.

Squeaky