CODEWITHSUNDEEP
arraysexcelexcel-formulaformula
user3601876
user3601876

Reputation: 47

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

Upvotes: 1

Views: 142

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34370

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 *.

Upvotes: 1

Related Questions