Lookup and return multiple matches in Excel Formula to return multiple matches. It is an array formula. Press Shift+Ctrl+Enter in the formula bar. =IFERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10="Excel",ROW($A$1:$A$10)),ROW(A1)),2),"") =IFERROR(INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$A$2:$A$6="A",ROW(Sheet1!$A$2:$A$6)),ROW(1:1))-1),"") =SUMPRODUCT((--MID($A$2:$A$4,FIND("#",$A$2:$A$4)+1,10))) It uses Index, Small and Row functions to perform the required. Excel Factor 17 Lookup and Return Multiple Matches
Excel formula to count if cell contains text Here is the excel formula to count if cell contains text based on the partial match of the cells. =COUNTIF(A1:A10,”*Excel*”) Ex:- Excel is great. I use Excel daily.
Convert Excel Table to Normal Range VBA Here is the vba code to convert Excel Table to Normal Range without manual operation. ActiveSheet.ListObjects(1).Unlist Expecting that, there is only one table exists in the sheet. http://vba.relief.jp/excel-vba-convert-table-to-range-of-cells/