Can you find that pattern? [Homework]
Are you ready for an Excel challenge?
Today, your job is very simple. Just find a pattern in a text and return corresponding value.
In a range we have some resource types & their billing rates.
In another range, we have some descriptions. Each description contains a resource type somewhere inside it. We need to retrieve billing rate for each description by looking up which resource type is mentioned in it.
See this diagram:
Download the data
Click here to download the data to write your formulas.
- The file contains 3 named ranges – resIDs, resTypes, resRates for resource IDs, types & rates in that order. You can use these to shorten your formulas.
Post your answers:
Go ahead and solve this. Then come back and post your formulas in comments. Click here to post your answer.
Read wildcards in COUNTIF formula page to get some clues.
So go ahead and post your answers. I am waiting…
Do you want to be awesome in Excel?
Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:
Your email address is safe with us. Our policies
More awesome tips for you:
Leave a Reply
|Highlight best week & month in a trend chart [tutorials]||Show hide list boxes using VBA|