Dec 04 2023 07:33 PM
Hi,
Is there a formula in excel which can lookup for a match in multiple tabs (around 6) and if it finds a match it should return the value summing up the values where a match was found
looking forward to some solution! thanks!
Dec 04 2023 11:15 PM
For Excel 365, you can use the new dynamic array functions, specifically SUM, FILTER, and IFERROR, to simplify the formula. Here is an example:
=SUM(IFERROR(FILTER(Sheet1:Sheet6!B:B, Sheet1:Sheet6!A:A = $A2), 0))
This formula takes advantage of the dynamic array functions available in Excel 365. The FILTER function is used to filter values based on the condition, and the SUM function sums up the filtered values.
Enter this formula in a single cell, and Excel 365 will automatically spill the result over adjacent cells if needed. Adjust the cell references and sheet names based on your actual data structure.
Remember that dynamic array functions are specific to Excel 365 and may not work in earlier versions of Excel. AI was partially deployed to support the text.
If this is not what you want or does not work with your version of Excel, I recommend adding more information. Information such as Excel version, operating system, storage medium, etc.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Dec 06 2023 08:35 PM
Dec 06 2023 09:27 PM
Solution@AKuma0411 The FILTER function only accepts three arguments. The basic syntax is:
=FILTER(array, include, [if_empty])
To SUM the results from multiple worksheets, try the following:
=SUM(
FILTER(UEPL_Final!$N$6:$N$200, UEPL_Final!$E$6:$E$200 = $C11, 0),
FILTER(LEPL_Final!$N$6:$N$200, LEPL_Final!$E$6:$E$200 = $C11, 0),
FILTER(IMN_Final!$N$6:$N$200, IMN_Final!$E$6:$E$200 = $C11, 0)
)
If the worksheet tabs are arranged in order from left to right, you could also try using the VSTACK function to reference and filter all three sheets at once. For example:
=SUM(FILTER(VSTACK(UEPL_Final:IMN_Final!$N$6:$N$200), VSTACK(UEPL_Final:IMN_Final!$E$6:$E$200) = $C11, 0))
Or, to improve readability, use the LET function to define the two arrays first:
=LET(
array1, VSTACK(UEPL_Final:IMN_Final!$N$6:$N$200),
array2, VSTACK(UEPL_Final:IMN_Final!$E$6:$E$200),
SUM(FILTER(array1, array2 = $C11, 0))
)
I hope that helps to clear things up. Cheers!
Dec 07 2023 06:36 AM
Unfortunately, FILTER does not support 3D references. VSTACK does and can be used to stack the data before filtering as @djclements has detailed.
Dec 07 2023 09:26 AM
Dec 06 2023 09:27 PM
Solution@AKuma0411 The FILTER function only accepts three arguments. The basic syntax is:
=FILTER(array, include, [if_empty])
To SUM the results from multiple worksheets, try the following:
=SUM(
FILTER(UEPL_Final!$N$6:$N$200, UEPL_Final!$E$6:$E$200 = $C11, 0),
FILTER(LEPL_Final!$N$6:$N$200, LEPL_Final!$E$6:$E$200 = $C11, 0),
FILTER(IMN_Final!$N$6:$N$200, IMN_Final!$E$6:$E$200 = $C11, 0)
)
If the worksheet tabs are arranged in order from left to right, you could also try using the VSTACK function to reference and filter all three sheets at once. For example:
=SUM(FILTER(VSTACK(UEPL_Final:IMN_Final!$N$6:$N$200), VSTACK(UEPL_Final:IMN_Final!$E$6:$E$200) = $C11, 0))
Or, to improve readability, use the LET function to define the two arrays first:
=LET(
array1, VSTACK(UEPL_Final:IMN_Final!$N$6:$N$200),
array2, VSTACK(UEPL_Final:IMN_Final!$E$6:$E$200),
SUM(FILTER(array1, array2 = $C11, 0))
)
I hope that helps to clear things up. Cheers!