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…
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Highlight best week & month in a trend chart [tutorials]||Show hide list boxes using VBA »|