Vlookup Function

[Guest]

I have a question on the Vlookup function. Can you set your table array to be
multiple worksheets in multiple workbooks? What I am trying to do is this. On
a worksheet, I have a file number column and a Y/N column. I am trying to get
the Y/N column to populate with either Y or N for that file number by
searching for that file number in three separate workbooks, each with several
worksheets, any of which the file number could be in. In all of these
worksheets, the file number could be in either Column "B" or Column "H".
There is also a Y/N column in these worksheets, in Columns "D" and "J". The
Y/N column values that I need for the file numbers in all the workbooks are
all in the same row as the file numbers. I know that Vlookup would be able to
return that value into the first worksheet, but I am unable to set the table
array to include all three other workbooks with all their worksheets. Is
there a way to set the table array to include all of these, or is there a
better way of doing this? Thanks for your help.

 

[Myrna Larson]

I don't believe so. You'll have to use IF to lookup in one workbook; if the
result is NA, lookup in the next, etc.

 

[Myrna Larson]

In my first response, I suggested nested IF statements. I missed the fact that
you say there are multiple worksheets in each of the 3 workbooks. I'm sure you
will exceed the limit of 7 nested IF statements. I think you need to set up a
single table that you create by using formulas to fetch the data from each of
the worksheets you need. i.e. =[Book1.xls]Sheet1!A1