Range Lookup in Excel – How to lookup the pricing tier? [Formulas]
Excel formula to get a match from a given value in excel when you have start and end values in lookup table. This technique is useful for looking up a matching price from a pricing tier table or date in a range of start and end dates.
Formula Forensics 042: Reverse Text – A Formulaic Solution
Use the new Textjoin() function to reverse a string of characters suing a formulaic solution.
An odd lookup problem [Formulas]
Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.
And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.
Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).
So how would you write the lookup formula?
Formula Forensics No. 041 – Convert a Roman Numeral to a Number
Learn how to convert a Roman Numeral to a Number using this nifty formula. No VBA required.
Formula Forensics No. 039 – Find the Cell Address for a value (2D & 3D Reverse Lookup)
Find the Cell Address for a value from a 2D or 3D Range
(2D & 3D Reverse Lookup)
3D Max Formula for Excel
We all know about the MAX formula. But do you know about 3D Max?
Sounds intriguing? Read on.
Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.
For one of the reports, you need to find out the maximum transactions by any customer across months.
But there is a twist in the story.
Your data is not in one sheet. It is in multiple sheets, one per month.
Formula Forensics No. 037 – How to Count and Sum Filtered Tables
How to Count and Sum data from Filtered Tables