Hi there,
In this post, I will be sharing a giant problem in excel. Even my client is in dilemma how to crack it. After giving him my solution, they were very happy because they are dealing with dollars.
My client has a database where the renewal amounts are placed next to customer ID's. So, our work is to deduct the renewal fee from the customer's account. You, as a reporting analyst will know how crucial this is upto. Finally, my client finished his job in couple of hours which was actually taking more than a week.
The scenario is, the client has a list of customer ID's where they are all case-sensitive. They look like this:
Sorry, if this was little messy.
...
The above pic defines you that in column B (Customer ID) the ID's are just similar till 10 digits but last few digits will have case sensitive.
If you observe first four ID's are the same but they are case sensitive, one in capital and the other one in small letter, similarly other ID's. To pull the renewal amount, VLOOKUP will not help us here because our functions are not case sensitive.
So, how to achieve the result?
I have two methods to achieve this challenge.
Method 1:
This requires 4 functions.
1. CODE
2. MID
3. IFERROR
4. VLOOKUP and it also requires an HELPER COLUMN.
Using this method, we can extract the code number for the letters, the code for capital A is 65 and the code for small a is 97, this way we can extract MID and using IFERROR we will avoid errors if any. Finally, we can pull the renewal amounts using VLOOKUP itself.
The final formula before using VLOOKUP looks like this:
=CODE(MID(F4,11,1))&CODE(MID(F4,12,1))&CODE(MID(F4,13,1))&CODE(MID(F4,14,1))&CODE(MID(F4,15,1))&IFERROR(CODE(MID(F4,16,1)),"")&IFERROR(CODE(MID(F4,17,1)),"")
with VLOOKUP (final formula) is here:
=VLOOKUP(CODE(MID(B4,11,1))&CODE(MID(B4,12,1))&CODE(MID(B4,13,1))&CODE(MID(B4,14,1))&CODE(MID(B4,15,1))&IFERROR(CODE(MID(B4,16,1)),"")&IFERROR(CODE(MID(B4,17,1)),""),$I$4:$J$14,2,0)
Similarly, we can use method 2 as well to achieve this result.
Method 2:
This requires only two functions,
1. SUMPRODUCT and
2. EXACT
Use this formula to extract the values.
=SUMPRODUCT(EXACT($F$4:$F$14,B4)*($G$4:$G$14))
That's it. Magic Works!!! Right?!
DID YOU USE CASE-SENSITIVE VLOOKUP?
Please do share your views and thoughts about this topic.
If you like this post, please do share it on your social networking sites by clicking on the icons placed around the blog.
If you want to receive such updates regularly to your email, REGISTER here for free.
SOME IMPORTANT LINKS
Thanks,
Recent comments