The VLOOKUP function in Excel is designed to search for a ‘Lookup Value’ in the first column of the specified Table and return the corresponding Value against the first matching Value. But what if there are two tables, one of which has the possible matching value? A single VLOOKUP function can’t lookup into two different…
This blog post is about an Excel formula which will return the Count of a particular Weekday or Weekend between two dates. The following formula will give us the Count of ‘Sundays’ between ’01/01/2022′ and ’31/12/2022′. =SUMPRODUCT(–(WEEKDAY(SEQUENCE(“31-12-2022”-“01-01-2022″+1,,”01-01-2022”))=1)) Let’s see how this formula works. Explanation of the Formula The SEQUENCE function will generate a list of…
In Excel, we have the VLOOKUP function to perform Lookup in Vertical direction (Rows) and the HLOOKUP function to Lookup in Horizontal direction (Columns). But for 2 Way Lookup? i.e. to perform Lookup in both columns and rows? No. Excel doesn’t have a dedicated function for this purpose. We should create the formula for 2…
In this tutorial, I will show you 5 different ways to Extract only Numbers from a list of Strings. Following is a Table containing Text as well as Numeric Values and we will be using this data for explaining the different methods. FILTER Function to extract Numbers The ISNUMBER function in Excel can be used…
Following are 3 different ways to segregate Positive and Negative values from list of numbers in Excel. MAX and MIN Function to separate Positive and Negative values The MAX function in Excel will return the largest among 2 or more numbers supplied into it. This property of MAX function is used below to check whether…
This blog post is about 10 different ways to find the Sum in Excel. We have a list of 5 numbers in the cells B3, B4, B5, B6 and B7 as shown below. Now let me show you the most common and uncommon methods for finding Sum in Excel. Using Addition Operator ‘+’ =B3+B4+B5+B6+B7 SUM…
This blog post is about combining the NOW function with the Conditional Formatting feature in Excel to highlight a cell or cells corresponding to Current Time. The following is a very common spreadsheet template used at working environments for recording manhours. Let’s see how to spot the cell corresponding to 10:23 A.M (Current Time) on…
In this blog post I will explain 3 different methods to find the Count of each Item in the list shown below. The methods include the use Pivot Table, Power Query and Formulas. Note that I have converted the list into an official Excel Table and named it as ABCD. Dynamic Array Formula to find…
This Excel tutorial is about 3 different methods to find the common values between two Lists or Tables in Excel. Here we have 2 lists of World’s Billionaires, one from the year 2015 and other one from 2020. Following are different methods to find the Names that are present in both lists. MATCH function to…
Paste Special to add a Number to multiple Cells The Paste Special feature in Excel helps us to perform Addition, Subtraction, Multiplication and Division with multiple cells and data ranges in Excel. In the following screenshot you can see two data ranges filled with numbers, B2:D11 and F5:J9. To add 10 to all those numbers…