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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
Following are 4 different methods to find the Sum of first 50 natural numbers using Excel. Formula using SUM and SEQUENCE function The following formula will return the sum of numbers from 1 to 50. =SUM(SEQUENCE(50,1,1,1)) For the sum of first 75 numbers, =SUM(SEQUENCE(75,1,1,1)) Formula using SUMPRODUCT and ROW function Formula that combines SUMPRODUCT and … Read more