Quick Reference

Knowledge Sharing

Lookup and return multiple matches in Excel

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    

Convert Excel Table to Normal Range VBA

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/

OR logic in Sumproduct function in Excel

OR logic in Sumproduct function in Excel Sumproduct usually will be applied with AND logic. OR logic in sumproduct function can be implemented as detailed below. A) AND condition example is as follows: =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target A")) =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target B")) B) There are ways to add OR condition using “+” symbol. The “plus sign” (+) is for OR in...

Display Times as Hours/Minutes in Excel

Display Times as Hours/Minutes in Excel Usually, Excel stores time as decimal values and displaying times as hours or minutes. Ex: A1=200 B1=A1/1440 (1440 = 24*60) = 0.1389 Now, use custom format as h:mm (or) h” hours and “m” minutes” There is an another option in Excel using Convert function.  =Convert(A1,”mn”,”hr”) -Ref: https://www.accountingweb.com/technology/excel/display-times-as-hoursminutes-in-excel

Minimum if multiple criteria using Excel

Minimum if multiple criteria using Excel Get the minimum value by checking multiple criteria from different columns. Minimum if multiple criteria is calculated with the following array formulas including nested if conditions. A) The following formula checks different criteria and get the minimum values from value column. =MIN(IF(rng1=criteria1,IF(rng2=criteria2,values))) Press Ctrl+Shift+Enter to convert to array...

Check, if a cell is in the list of ranges or not in Excel?

Check, if a cell is in the list of ranges or not in Excel? There are ways to search a text in range of cells using countif formula. The range can be specified using named ranges / specifying the ranges directly / using array formats. Here are the following examples that lists different ways...

Presence of number in a string using Excel Formulas

Presence of number in a string using Excel Formulas Use Find to check each occurrence of number in the array and count at the end. If the value is greater than 0, then it can be concluded that, cell B1 has number in it. The result is TRUE or FALSE. =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B1))>0 Few examples are...

How to use the SUM in Excel

How to use the SUM in different ways in Excel Description The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. The SUM function adds all numbers in a range of cells and returns the results. You can add individual values, cell references or ranges or a mix...

Extract phone numbers using Regex in Excel vba

Extract phone numbers using Regex in Excel vba Extract phone numbers from Excel using Regular expressions in the patterns (***) ***-****. In order to extract phone numbers, where cells are mixed with strings and phone numbers; Regular expression concept play an important role.   Option Explicit Sub ewqre() Dim str As String, n As...

Published On

December 2018
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930
31