Forum Discussion
FInding a cell with a particular word in, then putting the next row's figure into a new cell
The first part of your question simply requires the use of a wildcard criterion
= SUMIFS( Amount, Vendor, "*"& selected &"*" )
[the cell named 'selected' is assumed to contain "Paypal"]
To get a filtered list with inexact matches is harder. The start is to identify whether the selected string is to be found in each of the Vendors listed
= ISNUMBER( SEARCH( selected, Vendor ) )
For me, using Office 365 insider, I then simply nest the formula within the FILTER function
= FILTER( Amount, ISNUMBER( SEARCH( selected, Vendor ) ) )
In earlier versions of Excel the record numbers are first returned, then SMALL is used to filter out the mismatches and, finally, INDEX is used to look up the amounts.
- Peter thanks for the advice, I am unfamiliar with wildcards and will look into this as it is always good to increase knowledge with Excel. I will do my best to give this a go also.
Please find attached a copy of nabilmourad's workbook containing all the formula, including a copy of the original formula converted to employ defined names rather than direct references.
The final FILTER formula only works with Dynamic Array versions of Excel.
