NESTED IF

7 ALTERNATIVES TO NESTED IF FUNCTION

Crispo Mwangi | 16-Jun-17 | 11 Comments

IF function is one of the most used functions in Excel. In my opinion, it is the foundation of all programming and Excel’s formulae mastery.  However, it is also one of the most misused functions, especially Nested IF. Especially now with Excel 2007 and beyond, you can nest up to 64 IF functions to form complex, […]

FIND N using multiple criteria

FIND THE LAST OR Nth OCCURENCE IN EXCEL USING MULTIPLE CRITERIA

Crispo Mwangi | 3-Feb-17 | Leave a Comment

In the previous article, we looked at 7 ways to find the last or Nth occurrence in a Sorted list in excel. Unsorted list poses a challenge since you need to find the occurrence using multiple criteria. To show the different methods I have created a list of Customers and their Purchase Orders (P.O). You can download […]

FIND NTH IN EXCEL

FIND THE LAST OR Nth OCCURENCE IN EXCEL (SORTED LIST)

Crispo Mwangi | 27-Jan-17 | Leave a Comment

Finding the Nth or the Last value in a sorted or unsorted list can pose a challenge if you do not understand which functions to use. This article will show you different ways to carry out this sort of find and retrieve in a sorted list. How to handle unsorted list will be tackled in […]

highlight123

Highlight a Sample In a Range or Text that contain certain Values

Crispo Mwangi | 14-Jan-17 | Leave a Comment

Learning how to use conditional formatting in excel can save you a lot of time when you need to visually highlight important information in a worksheet. At basic level, it can be used to highlight duplicates, values within certain threshold, Top or Bottom N items etc.  However, to get the full potential of conditional formatting, you […]

hlookup2

HLOOKUP, INDEX & MATCH OR VBA

Crispo Mwangi | 8-Oct-16 | 2 Comments

Having done a series on Verticle lookup  it is only fair to touch on Horizontal lookup. HLOOKUP function is the most popular function for this kind of lookup but it has a number of limitations and thus one may need to know a few other ways you can carry out a Horizontal lookup. For example, […]

REVERSE LOOKUP

REVERSE LOOKUP USING VBA

Crispo Mwangi | 11-Sep-16 | Leave a Comment

This is the last article in the series of reverse lookup in excel. Unless you are already versed with Reverse Lookup, consider checking below 2 articles first; Reverse Lookup & Reverse Lookup with duplicates Now using VBA we are going to lookup the date, time and Jobs assigned to Joy Bell Go to Developer→Visual BAsic→Insert […]

Reverse lookup with duplicates

REVERSE LOOKUP WITH DUPLICATES IN EXCEL

Crispo Mwangi | 7-Sep-16 | Leave a Comment

It is good to be able to do a Reverse Lookup but it is Awesome if you know how to return Multiple Items. Before you continue with the current article, Please revist the Previous Article on reverse lookup as this is a continuation. Suppose you are managing a project and you have below scheduled dates, […]

reverse LOOKUP

REVERSE LOOKUP IN EXCEL

Crispo Mwangi | 3-Sep-16 | 2 Comments

In my research and tutoring excel, I have found that many  people find Reverse lookup concept to be among the top 10 complicated things in excel. This article hopes to shed more light than heat in demystifying the Reverse Lookup in excel. In the normal lookup, we use the Column and/or Row header to return the value that […]

2way lookup

2 WAY LOOKUP IN EXCEL

Crispo Mwangi | 20-Aug-16 | 2 Comments

The ability to lookup values is a MUST have skills for all excel users. If you cannot lookup you cannot excel in data analysis. In this article I will show you  5  ways you can do a 2 way lookup in Excel. 1. USING SUMPRODUCT For example using below sales data lookup and sum the total quantity for ALL […]

lookup and Sum

LOOKUP and SUM IN EXCEL

Crispo Mwangi | 7-Aug-16 | Leave a Comment

Array fomulas comes in handy when you want to lookup and sum large amount of data. Array formulas replaces multiple normal formulas,performs multiple calculations and returns a single value as a result. For example using  below Main sales data in Quantity and a lookup price table, Calculate total sales for customer Brosina Hoffman?   There […]