If, then, vlookup

A

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.
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top