If, then, vlookup
 

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.

If, then, vlookup
 

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

"akemeny" wrote:

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.