HLOOKUP

HLOOKUP with multiple criteria (OR/AND logic)

Crispo Mwangi | 12-Aug-17 | Leave a Comment

For some time now, I have looked for a way to look up horizontally duplicate data using (OR/AND) logic. All the attempts to use HLOOKUP to return multiple results using (OR/AND) logic failed. Then I discovered a combination of  INDEX, SMALL, IF and ROW functions that worked like magic. Suppose you have below attendance register and you want […]

REPT IMAGE

7 WAYS TO USE EXCEL REPT FUNCTION

Crispo Mwangi | 29-Jun-17 | 4 Comments

When is the last time you used REPT function in Excel? REPT  is one of excel’s little-known, overlooked and underutilized function, yet very useful. Generally, REPT  returns a specific text string a specified number of times. =REPT(“Text”, Number of times) Here are 7 ways you can start using REPT function. ADD LEADING ZEROS CREATE INLINE […]

NESTED IF

7 ALTERNATIVES TO NESTED IF FUNCTION

Crispo Mwangi | 16-Jun-17 | 13 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 […]