Search code examples
arraysexcelexcel-formulaformula

Excel Formula: How to loop through an array and check if part of another list

I am wondering if it is possible - using an Excel formula (no vba) - to loop through a list with values and check if those values are in another list. The idea is to use this to come up with a sum pulling data from the first list while the second list sets the conditions. Please see below picture. I was thinking that this might work with sumproduct including an if-statement. Any ideas?

example_visualized

Solution

  • This should work (F1):

    =SUM(SUMIF(Table1[Item],Table2[Included],Table1[Amount]))

    but you may need to array-enter it or use SUMPRODUCT instead of SUM in pre-365 versions of Excel.

    enter image description here

    If you wanted to match ? literally rather than as a wild card standing for any single character you would need F2:

    =SUM(SUMIF(Table1[Item],SUBSTITUTE(Table2[Included],"?","~?"),Table1[Amount]))

    and similarly for *.