Excel/Excel 365 - =FILTER() function fallback

Question

On Excel365, I am using a basic FILTER formula to retrieve data from a different Workbook (Workbook2) based on the cell content inside Workbook1.

For a given input in cell A1, which is a fiscal year (e.g. FY64), I apply a filter on columns D through to G. This works fine.

=FILTER('[Workbook2]Info'!$D:$G,'[Workbook2]Info'!$A:$A='Workbook1'$A1ドル)

However, I have a Workbook3 with data for the previous year (FY63).

If in Workbook1, A1 = FY63, is it possible to have the filter check Workbook2, realise there is no matching line, then fallback to Workbook3?

I tried creating an IF formula but was unable to retrieve the information. Formulas like FILTER(Formula)*FILTER(Formula) return a #CALC error.

Answer 1

This does not look like it is specific to having data in other workbooks.
The solution lies in one of the parameters for the Filter function, more precisely if_empty.

With a LET to declare the search value once and use it twice, this would give you:

=LET(SearchValue, $A1,ドル
 FILTER('[WORKBOOK2]Info'!$D:$G,F1:F11=SearchValue,
 FILTER('[WORKBOOK3]Info'!$D:$G,I1:I11=SearchValue)
 )
)

---

With that said, I would like to recommend you try to import data from your 2 workbooks into the main one, something in the lines of:

1. Go to the menu Data, Get data > From file > From Excel Workbook, pick the range you desire in WORKBOOK2. The result is a query called (by default) Table1
2. Do the same with WORKBOOK3. You will get Table2 from it.
3. Create an empty query to combine Table1 and Table2 without duplicate (With data fromTable1 having higher priority). You did not give the header of your tables but the code for that combined table will be in the lines of:

   let
    Source = Table.Combine({Table1, Table2}),
    #"Removed Duplicates" = Table.Distinct(Source, {"Header1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Header1", Order.Ascending}})
   in
    #"Sorted Rows"
   

4. If needed, edit the query properties to tick "Refresh data when opening the file"

This is more complicated to set up than the first solution but makes it easier to use (A single FILTER is all it takes) and it avoids having to search in separate files every time you change the value in $A1ドル.

Especially, you'll need to change all the formulas in WORKBOOK1 if you one day want to e.g. rename WORKBOOK2 or WORKBOOK3 files or add WORKBOOK4. With the queries, there is only 1 place you have to update for these operations.

• DanGee: Good morning Atmo! Thank you for your answer, I can confirm that the first formula you had placed had worked a charm and is giving me the information i require from Workbook 2 & 3 if I change the 'SearchValue'. I will look into the 2nd formula you posted as you mentioned this will make it easier to use in the long run!