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 […]

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 […]

Overlooked uses MOD function

7 Overlooked Uses of Excel MOD Function

Crispo Mwangi | 7-Jun-16 | 10 Comments

In Over  450 functions in Excel, there are some powerful but highly underutilized functions and some overly popular functions too. Functions like IF, SUM, COUNT, AVERAGE are well known by all excel users. Yet there are some functions like MOD, WEEKDAY, WORKDAY, CHOOSE, DATEDIF, DELTA, SUBSTITUTE, HYPERLINK are rarely used. This is part 1 of a […]