vlookup Over Multiple Sheets

[Francis]

I've compiled a document with 13 sheets, the first for searching, the next 12 for data for each month of the year. The search sheet will contain multiple vlookup functions.

I have been trying to get a vlookup function to search across these multiple sheets by using an if function - if the vlookup result is nothing on the first sheet, have a look at the next and so on. But I can not seem to get it to work. Am I going about this the wrong way? Is there a function that would make these easier?

Any help would be appreciated.

[RickRandom]

I don't know of such a function.

Rather than use one huge nested IF() function, at least to build up the logic you require in manageable chunks that you can fault find, I suggest:

1. Put the VLOOKUP() for the January(?) sheet in one cell, the VLOOKUP() for the February sheet in the next cell, etc., let's assume B2, C2, etc.

2. If the VLOOKUP() doesn't find what you want in January, it returns #N/A, so you can use the function ISNA() in an IF() function, e.g. in the cells above the VLOOKUP()s, to identify which VLOOKUP() found what you wanted, e.g. in B1:

=IF(ISNA(B2);"Not Found";"Found")

and fill across 12 columns.

3. Provided you just want the first find, then use HLOOKUP() across the range of Found/Not Found and the VLOOKUP()s, e.g. if the first Found/Not Found is in B1, the last VLOOKUP() in M2:

=HLOOKUP("Found";B1:M2;2;false)

(I admit I haven't tried this, so if you can't make it work, post again.)

Also, I think there is a limit to how many IF() functions you can nest, and that it might well be less than 12.

I think the step by step is more likely to work, and more likely to work without loads of head scratching.

[Villeroy]

Splitting data across many sheets and files is common (but bad) practice.
Keep everything in one single unsorted list and let the program do the separation by time-intervals. Use filters, pivot-tables(data pilot), sheet-functions (YEAR,QUARTER,WEEK,MONTH,DSUM,DMAX,DCOUNT,D...) consolidation and subtotals. (A database can do even better).
Anyway:
You may define a named range including 12 ranges separated by semicolon:
myMonthLookup =$Jan.$A$1:$D$65536;$Feb.$A$1:$D$65536;...;$Dec.$A$1:$D$65536
Then you may lookup some value in third column of Dec like this:
=VLOOKUP(value;INDEX((myMonthLookup);0;0;12);3;0)
Read the help on INDEX() function.
INDEX((reference);row_Index;col_Index;SubRange)
(myMonthLookup) needs braces in this case
col_index 0 means: no specific column (missing argument)
row_index 0 means: no specific row (missing argument)
SubRange 12 is the 12th range in your range-list.

[Francis]

Thanks for the quick reply and helpful tips. Unfortunately the search sheet with all of the vlookup functions in is designed to be printed and contains about 30 different entries. What I've thought about doing is to construct another sheet which contains all of the data, for the whole year and use MONTH to extract the individual monthly data and insert it on the monthly sheets. This way I can use VLOOKUP on the one sheet, the monthly sheets will be there just for show.

Thanks again.

[David]

You might try plain LOOKUP(). ....

=LOOKUP(D1;Sheet2.A1:Sheet2.A3;Sheet3.A1:Sheet3.A3)

For example; if sheet 2 has A,B,C in column A, and sheet 3 has X,Y,Z in column A, and if sheet 1 cell D1 has "B", the statement looks up D1's content in sheet 2 [second row entry is a B] and returns the same row [second] content from sheet 3. That is, sheet 1 cell A1 [or wherever the formula is placed] will show a Y.

David.

[Villeroy]

When filtering by months you can redirect the output to another sheet (see options of Standard-Filter dialog), creating your month-reports on the fly.
Another hint on using filters:
http://www.oooforum.org/forum/viewtopic.phtml?t=35536

[Villeroy]

Just a short description how to use the data pilot:
Having your complete list with all data in columns A to F and dates in colun A, add two columns:
G1: Year
G2: =Year($A2)
E1: Month
E2: =$G2&TEXT(MONTH($A2);"00")
drag down G2:E2
Select A to E
Menu:Data>Pilot
Target-Cell : A1 of some unused sheet.
Other options: ON (they won't hurt )
Drag Month and Year to "Page Fields"
Other Fields to "Row Fields"
If you want to show an aggregation of some field (SUM, Count or alike) drag the field to "Data Field"
Play around with the resulting table . You can drag the grey boxes on the sheet.
Make 11 copies of the sheet and select a different month from the Month-dropdown.