SubscribeCan you find that pattern? [Homework]
Hi folks…
Are you ready for an Excel challenge?
Today, your job is very simple. Just find a pattern in a text and return corresponding value.
Your Homework:
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.
Notes:
- 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.
Need clues?
Read wildcards in COUNTIF formula page to get some clues.
So go ahead and post your answers. I am waiting…
| ||||
|
| ||||
|
Leave a Reply
 |
Highlight best week & month in a trend chart [tutorials] | Show hide list boxes using VBA |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
119 Responses to “Can you find that pattern? [Homework]”
Not my formula but this will do the trick…
=LOOKUP(2^15,SEARCH(resTypes,B4),resRates)
~VijaySharma
Great solution! Trying to figure out how it works. Could you explain? especially the 2^15 part.
Peter
Seems like here might be an answer:
The 2^15 is the lookup value and needs to be large enough to be greater then the length of the text in B4, since the search function will return #VALUE! for the search terms not matched and the position of the matched search term.
Effectively, the lookup becomes =LOOKUP(2^15,{#VALUE!;#VALUE!;#VALUE!;9;#VALUE!;#VALUE!},resRates)
2^15 is an arbitrary number for this example. The largest position that SEARCH returns in this example is 16, which can be used in the example.
This formula works unless you have a Resource Description of ”FM Radio Station AM xxxxx2324″ or anything combination before the resource type that matches a different resource type. So in my example there is FM which is not the type in this description but the formula will still pick it up as the type and bring back a 75 instead of a 50.
I love this one however it fails if some string appears in a word
I would add a trailing space (and leading ?).
=LOOKUP(2^15;SEARCH( esTypes & ” “;B4);resRates)
Then you can use this one…
=LOOKUP(2^15,SEARCH(resTypes & ” xxx”, B4 & ” xxx”), resRates)
That’s close, I think. But if there is a couple of research types like PM and PPM then you’d still get the wrong answer.
<code>=LOOKUP(2^15, SEARCH(” ” & resTypes & ” “, B4), resRates)</code>
I think this’d take care of it.
Almost
=LOOKUP(2^15,SEARCH(” ” & resTypes & ” xxx”, ” ” & B4 & ” xxx”), resRates)
Uuups, no you are right…
in fact the best & right answer
array formula
=VLOOKUP(INDEX($H$4:$H$9,MAX(IF(ISERROR(FIND($H$4:$H$9,B4)),-1,1)*(ROW($H$4:$H$9)-ROW($H$4)+1))),$H$4:$I$9,2,FALSE)
=RECHERCHEV(SUPPRESPACE(DROITE(GAUCHE(B4;TROUVE(“xx”;B4)-2);3));$H$4:$I$9;2;0)
=Vlookup(trim(right(left(b4;find(“xx”;b4)-2);3));$h$4:$i$9;2;0)
Formula is too long but it works
=VLOOKUP(MID(B4,IF(ISERROR(FIND(“ngs”,B4)),FIND(“ng”,B4)+3,FIND(“ngs”,B4)+4),2)&”*”,$H$4:$I$9,2,0)
~lilg
What i’ve figured out is:
{=INDEX(resRates;SUM(NOT(ISERROR(SEARCH(resTypes;B4;1)))*ROW(resTypes))-3)}
Here is my solution (array formula):
INDEX(resRates,MATCH(0,FIND(resTypes,B4,1)*($J$4:$J$9),0))
Hi,
I have used countif & If conditions, Please do let me know if we have any other better way…
IF(COUNTIF(B4,”*”&$H$4&”*”)<>0,$I$4,IF(COUNTIF(B4,”*”&$H$5&”*”)<>0,$I$5,IF(COUNTIF(B4,”*”&$H$6&”*”)<>0,$I$6,IF(COUNTIF(B4,”*”&$H$7&”*”)<>0,$I$7,IF(COUNTIF(B4,”*”&$H$8&”*”)<>0,$I$8,IF(COUNTIF(B4,”*”&$H$9&”*”)<>0,$I$9,”Check”))))))
Looking forward to learn a lot from here…..
Cheers…
1. INDEX(resRates,MATCH(1,–(ISNUMBER(FIND(resTypes,B4))),0))
2. INDEX(resRates,SMALL(IF(1*(ISNUMBER(FIND(resTypes,B4))),ROW(resTypes)-ROW($H$4)+1),1))
Both required Ctrl+shift+Enter
I like Vijay’s best so far, but this is what I first came up with. Maybe I just love using SUMPRODUCT too much….
=SUMPRODUCT(ISNUMBER(SEARCH(resTypes,B4))*(resRates))
Yes Luke You Do Love Very much of Sumproduct
sumproduct is useful function which was developed by excel user like us…and idea apply by MS in excel 2007 as sumifs……:D
GFC variation :
=RECHERCHEV(SUBSTITUE(DROITE(GAUCHE(B4;CHERCHE(“xx”;B4)-2);3);” “;”");$H$3:$I$9;2;0)
array formula
=SUM((IFERROR(SEARCH(resTypes,b4),0)<>0)*resRates)
I too like Vijay’s LOOKUP formula since it very short and to the point!
(I would wrap the restypes in a space on either side to avoid any false positives.)
Here is what I came up with:
=INDEX(resRates, MATCH(99,FIND(” ” & resTypes & ” “, B4)))
Ctrl + Shift + Enter
The idea is similar to a few of the formulas already posted.
Cheers,
Sajan.
Looks like I’m the only one that went the vba route with this one. I think I got a good handle on formulas since been using them since forever, so always look for ways to improve my vba skills. Well anyways, here it is.
https://www.dropbox.com/s/qipm1hoczmvrxgv/can-you-find-the-pattern%20NMone.xlsm
=OFFSET(resRates,MAX((IFERROR(FIND(resTypes,B4,1),0)>1)*resIDs)-1,0,1,1)
=OFFSET(resRates,MAX((IFERROR(FIND(resTypes,B4,1),0)>1)*resIDs)-1,0,1,1)
Ctrl+Shift+Enter of course.
=VLOOKUP(TRIM(CLEAN(RIGHT(MID(B4,1,FIND(“x”,B4)-1),4))),$H$3:$I$9,2,0)
=IF(NOT(ISERROR(SEARCH($H$4,B4))),$I$4,IF(NOT(ISERROR(SEARCH($H$5,B4))),$I$5,IF(NOT(ISERROR(SEARCH($H$6,B4))),$I$6,IF(NOT(ISERROR(SEARCH($H$7,B4))),$I$7,IF(NOT(ISERROR(SEARCH($H$8,B4))),$I$8,IF(NOT(ISERROR(SEARCH($H$9,B4))),$I$9))))))
=INDEX(resRates,MATCH(TRIM(LEFT(RIGHT(B4,LEN(B4)-FIND(” x”,B4)+4),3)),resTypes,0),1)
=SUMPRODUCT(COUNTIF(B4,”*”&resTypes&”*”)*resRates)
Assign a code relevant to match with resource type in column A by filtering text contain resource type……then use simple forlmula…. =VLOOKUP(A4,$H$4:$I$9,2,FALSE) …
=VLOOKUP(SUBSTITUTE(MID(B4,FIND(“x”,B4,1)-4,3),” “,”"),$H$4:$I$9,2,0)
Awesome nonfancy formula
Two solutions.
=LOOKUP(2,1/COUNTIF(B4,”*”&resTypes&”*”),resRates)
=LOOKUP(9^9,SEARCH(resTypes,B4),resRates)
=SUMPRODUCT(1-ISERROR(FIND(resTypes,B4)),resRates)
Good answer..elegant solution. So, by using the Sumproduct function, the Find function can handle an array of values that it normally could not do outside of the Sumproduct function??? Thanks for the post.
I couldn’t figure out how to do it without a “helper” column, (I kept running into errors when I tried to nest VLOOKUP. So I just created a nested IF formula in the E column to tell me the resource’s ID:
=IF(ISNUMBER(FIND(“AM”,B4)),1,IF(ISNUMBER(FIND(“PM”,B4)),2,IF . . . etc.
Then, I just used a simple VLOOKUP with a reference to the ID in column E:
=VLOOKUP(E4,$G$4:$I$9,3)
Not the most elegant solution. I’m looking forward reading the awesome that all of you used to solve this.
=SUMPRODUCT(–(TRIM(MID(B4,FIND(“x”,B4)-4,3))=(resTypes)),resRates)
Similar to others looking left of x’s
=VLOOKUP((TRIM(MID(B4,TRIM(FIND(“x”,B4,1)-4),3))),$h$4:$i$9,2,FALSE)
=IF(COUNTIF(B4,”*CM*”)=1,VLOOKUP(4,RANGE1,3,FALSE),(IF(COUNTIF(B4,”*CXO*”)=1,VLOOKUP(6,RANGE1,3,FALSE),(IF(COUNTIF(B4,”*RM*”)=1,VLOOKUP(5,RANGE1,3,FALSE),(IF(COUNTIF(B4,”*AM*”)=1,VLOOKUP(1,RANGE1,3,FALSE),(IF(COUNTIF(B4,”*PM*”)=1,VLOOKUP(2,RANGE1,3,FALSE),(IF(COUNTIF(B4,”*FM*”)=1,VLOOKUP(3,RANGE1,3,FALSE))))))))))))
very elegant!
Haha, far from pretty, far from elegant, and FAR from dynamic, but it works:
=IF(NOT(ISERROR(FIND($H$4,$B4,1))),$I$4,IF(NOT(ISERROR(FIND($H$5,$B4,1))),$I$5,IF(NOT(ISERROR(FIND($H$6,$B4,1))),$I$6,IF(NOT(ISERROR(FIND($H$7,$B4,1))),$I$7,IF(NOT(ISERROR(FIND($H$8,$B4,1))),$I$8,IF(NOT(ISERROR(FIND($H$9,$B4,1))),$I$9,”N/A”))))))
Man, all of you guys’ other answers are quite impressive. Mine I thought was pretty simple, yet works perfectly. Let me know what you think…
=IFERROR( INDEX( resRates, MATCH( MID( B4, (FIND( “M”, B4) -1), 2), resTypes, 0), 1), 250)
Took two approaches
1. created a separate table setting the “Resource Type” than using that table for VLOOKUP reference
TRIM(CLEAN(MID(B4,FIND(“x”,B4)-4,3)))
followed by
IFERROR(VLOOKUP(L4,rate,2,FALSE),”")
OR….
2. A singular VLOOKUP combining efforts:
=IFERROR(VLOOKUP((TRIM(CLEAN(MID(B4,FIND(“x”,B4)-4,3)))),rate,2,FALSE),”")
Thank you that was fun.
=SUM(resRates*COUNTIF(B4,”*”&resTypes&”*”))
Array entered
WOOOOW!!!!
Mike,
LOVE IT, LOVE IT,,simplicity
Looks simples, but I get zero for value when I plug the formula into cell C4 and down. Anyone else has that problem?
Even I am getting Zero when i plug the same formula in C4.
Can you assist?
I exploited the commonality of the resource desc field values, and then did a little bit trial and error… with “things” and “thing” there are some offshoot of number of characters, fortunately you have spacebar in between thing/things and xxx… thus trim worked.
=VLOOKUP(TRIM(MID(B4,FIND(“thing”,B4,1)+LEN(“thing”)+1,FIND(“x”,B4,1)-FIND(“thing”,B4,1)-LEN(“thing”)-2)),$H$3:$I$9,2,FALSE)
Thanks, Somak
Excellent answers everyone… Here is my solution.
{=INDEX(resRates,MAX(COUNTIF(B4,”*”&resTypes&”*”)*(resIDs)))}
Solution file.
Why not just this array-entered formula without the INDEX part?
=MAX(COUNTIF(B4,”*”&resTypes&”*”)*resRates)
Really good suggestion, Rick, but I think it only works if all array elements are numeric. If result is non-numeric then INDEX or something similar will be needed.
I got answer…
{=VLOOKUP(“*CM*”,H4:I9,2,0)}
If there is no match the result is wrong (for example: Description “Few things xxxx5633″, gives the answer: 50
There is no AF In this description.
That because we get a zero vector to maximize.
=IF(COUNTIF(B4,”*M*”),VLOOKUP(MID(B4,FIND(“M”,B4)-1,2),$H$4:$I$8,2,0),$I$9)
There was a similar case on Chandoo forum.
Here it is:
http://chandoo.org/forums/topic/search-for-a-word-string-and-return-that-string-if-found
=INDEX($H$4:$I$9,MATCH(MID(SUBSTITUTE(B5,” “,”@”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,”"))-1),SEARCH(“@”,SUBSTITUTE(B5,” “,”@”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,”"))-1))+1,SEARCH(” “,SUBSTITUTE(B5,” “,”@”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,”"))-1),SEARCH(“@”,SUBSTITUTE(B5,” “,”@”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,”"))-1)))-SEARCH(“@”,SUBSTITUTE(B5,” “,”@”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,”"))-1))-1),resTypes,0),2)
SUMP RODUCT IS SIMPLEST
HI
I have taken Combination of Left, Right,Find & Trim with VLookup
=VLOOKUP(TRIM(RIGHT(LEFT(B4,FIND(” x”,B4)-1),3)),$H$4:$I$9,2,0)
i am totally agree with Vijay Shrama solution. Here is i am explaining the trick
=LOOKUP(16,SEARCH(resTypes,B4),resRates)
The text length of starting CM,AM etc is maximum 16, so this formula works also by putting 16. Any how if you not now the length than the best maximum digit should be enter.
Dear Chandoo
you are working really awesome, i am in very initial stage. i like your tricks. I started Excel about 8 months ago. From your blog and Mr, Excel is fun i have learned a lot.
also i request you if you will guide me how can i improve my self.
Thanks & Regards
Muhammad Shakeel
is the simple and best…….
Description
LEN
Nothing C
9
Few things P
12
Lots of things A
16
Something P
11
One thing A
11
Another thing C
15
correction
Description LEN
Nothing C 9
Few things P 12
Lots of things A 16
Something P 11
One thing A 11
Another thing C 15
Hi everyone,
May I suggest the following array formula :
{=INDEX(resRates,MAX(ISNUMBER(IFERROR(FIND(” ” & resTypes & ” “,B4),”"))*resIDs))}
As you could notice :
I did not use any wild card.
I supposed the ‘codes’ were had no blank space in front and/or behind (e.g. CM, not [ CM], [ CM ] or [CM ]
I wanted to avoid that any code could be found inside another word (in col B)
I finally could find the trick to get it work whatever the length of the code
Congratulations and Thanks to you all
Have a great week end
A Frenchie
=VLOOKUP(“*”&H4&”*”,$B$3:$D$33,3,0)
=MAX(IFERROR((SEARCH(“*”&resTypes&”*”,B4))*1*(resRates),0))
=VLOOKUP(TRIM(MID(B4,LARGE(IF(MID(B4,ROW($A$1:$A$50),1)=” “,ROW($A$1:$A$50),0),2)+1,3)),$H$4:$I$9,2,0)
Array-entered.
Meanwhile Waiting
Hi again,
Here’s another suggestion
=INDEX(resRates,SUM(–ISNUMERIC(FIND(” ” & resTypes & ” “,B4))*resIDs))
As an array formula (CTRL+SHIFT+Enter)
Seems to work well …
=MAX(IFERROR((SEARCH(resTypes;B4)/SEARCH(resTypes;B4))*resRates;0))
Hi
using with array formula
=MAX(IFERROR(SEARCH(resTypes,B4)/SEARCH(resTypes,B4)*(resRates),0))
CTR+SHFT+ENTER
Not working
This one as well:
=INDEX(resRates,SUM(IF(IFERROR((SEARCH(resTypes,B4)*ROW($A$1:$A$6))>0,0),ROW($A$1:$A$6))),0)
Ctrl+Shift+Enter
Regards,
Hi All,
This one as well:
=INDEX(resRates,SUM(IF(IFERROR((SEARCH(resTypes,B4)*ROW($A$1:$A$6))>0,0),ROW($A$1:$A$6))),0)
Hi
This one as well
=INDEX(resRates,SUM(IF(IFERROR((SEARCH(resTypes,B4)*ROW($A$1:$A$6))>0,0),ROW($A$1:$A$6))),0)
=VLOOKUP(TRIM(MID(B4,FIND(“x”,B4)-4,3)),$H$4:$I$9,2,0)
{=SUMPRODUCT(IF(IFERROR(FIND($H$4:$H$9;B4);0)>0;1;0);$I$4:$I$9)}
=VLOOKUP(TRIM(MID(B4,FIND(” x”,B4,1)-3,3)),$H$4:$I$9,2,FALSE)
Came up with this in a couple min. It works but it’s not as generic as I’d like.
I ended up with the following array formula
=SUMPRODUCT(IF(ISNUMBER(FIND(resTypes,B28))=TRUE,1,0),resRates)
This will work:
=LOOKUP(2^10,SEARCH(resTypes,B4),resRates)
I came up with this… I see many have similar entries
=SUMPRODUCT(IF(IFERROR(FIND(resTypes,B4),0)=0,0,1)*resRates)
One thing I would suggest to people who have used SEARCH in their formula. SEARCH isnt case-sensitive so in a case like this, it would be better to use FIND.
I have got the correct results, the following is the formula :
VLOOKUP(TRIM(IF(ISERROR(IF(ISERROR(MID(B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1)-FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1))=TRUE),MID(B4,L4+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)-FIND(” “,B4,FIND(” “,B4,1)+1)),MID(B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1)-FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)))=TRUE),MID(B4,FIND(” “,B4,1)+1,FIND(” “,B4,FIND(” “,B4,1)+1)-FIND(” “,B4,1)),IF(ISERROR(MID(B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1)-FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1))=TRUE),MID(B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)-FIND(” “,B4,FIND(” “,B4,1)+1)),MID(B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1)-FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1))))),$H$4:$I$9,2,0)
I got it, following is the formula :
=VLOOKUP(TRIM(IF(ISERROR(IF(ISERROR(MID(B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1)-FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1))=TRUE),MID(B4,L4+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)-FIND(” “,B4,FIND(” “,B4,1)+1)),MID(B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1)-FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)))=TRUE),MID(B4,FIND(” “,B4,1)+1,FIND(” “,B4,FIND(” “,B4,1)+1)-FIND(” “,B4,1)),IF(ISERROR(MID(B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1)-FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1))=TRUE),MID(B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)-FIND(” “,B4,FIND(” “,B4,1)+1)),MID(B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1)+1)-FIND(” “,B4,FIND(” “,B4,FIND(” “,B4,1)+1)+1))))),$H$4:$I$9,2,0)
=TRIM(MID(B4,IFERROR(IFERROR(IFERROR(SEARCH((“ing ?? xx”),B4)+5,SEARCH((“ings ?? xx”),B4)+5),SEARCH((“ing ??? xx”),B4)+5),SEARCH((“ings ??? xx”),B4)+5)-1,SEARCH(“xx”,B4)-IFERROR(IFERROR(IFERROR(SEARCH((“ing ?? xx”),B4)+5,SEARCH((“ings ?? xx”),B4)+5),SEARCH((“ing ??? xx”),B4)+5),SEARCH((“ings ??? xx”),B4)+5)))
my solution is also long
=VLOOKUP(IF(MID(B4,FIND(“x”,B4)-1-3,1)=” “,MID(B4,FIND(“x”,B4)-1-2,2),MID(B4,FIND(“x”,B4)-1-3,3)),$H$3:$I$9,2,0)
=INDEX(resRates,MATCH(TRUE,IFERROR(FIND(resTypes,B5),0)<>0,0))
Ctrl+Shift+Enter for array formula
Hi chandoo,
although i havent use countif function, but i have found an alternative to this problem. my only concern is that it took me 15-20 mins atleast for arriving at this solution which to too much. if u cud sugest a shorter or more simpler solution to it, i shall be grateful.
=VLOOKUP(IF(MID(B8,FIND(“x”,B8)-4,1)=” “,MID(B8,FIND(“x”,B8)-3,2),MID(B8,FIND(“x”,B8)-4,3)),$H$3:$I$9,2,0)
Hi chandoo,
although i havent use countif function, but i have found an alternative to this problem. my only concern is that it took me 15-20 mins atleast for arriving at this solution which to too much. if u cud sugest a shorter or more simpler solution to it, i shall be grateful.
Late to the party but here is what I came up with
=MAX(IFERROR(MATCH(“*”&$H$4:$H$9&”*”,B4,0),0)*resRates)
Entered as an array:
=VLOOKUP(MATCH(1,COUNTIF(B4,”*”&resTypes&”*”),0),$G$4:$I$9,3,0)
Not as elegant/straightforward as some of the others, but does not rely on the entry of an arbitrarily large value like 2^15.
Quite late though , This formula gave me desired result :
=VLOOKUP(TRIM(IF(FIND(“ing”,B4,1),MID(B4,IFERROR(FIND(“ings”,B4,1),FIND(“ing”,B4,1))+4,FIND(” x”,B4)-(IFERROR(FIND(“ings”,B4,1),FIND(“ing”,B4,1))+4)),IF(FIND(“ings”,B4,1),MID(B4,IFERROR(FIND(“ings”,B4,1),FIND(“ing”,B4,1))+5,FIND(” x”,B4)-(IFERROR(FIND(“ings”,B4,1),FIND(“ing”,B4,1))+5))))),$H$4:$I$9,2,0)
=VLOOKUP(TRIM(RIGHT(LEFT(B4,FIND(” xxx”,B4,1)),4)),$H$4:$I$9,2,FALSE)
hi,
TO Vijay:
Is it mendatory to put 2^15.
if i want to try with another string.
i.e.
PJEAL-S-JY13551-12A1
In that string i want 12a1.
no doubt with your formula i m getting result. but i want to understand the fundamental about “2^15″.
Hope you will understand.
Regards
Istiyak
You don’t need to use 2^15. You just need to use a number that is bigger than the size (number of characters) of your largest string. If your strings are always less than 30 characters, you can use 30 instead of 2^15. If you don’t know the size of your strings or you want to use a number that fits all situations, use the max number of characters a cell can hold (which is 32768 = 2^15).
=VLOOKUP(TRIM(MID(B4,FIND(“x”,B4)-4,4)),$G$4:$H$9,2,FALSE)
where B4 is the search string, and $G$4:$H$9 is the vlookup range.
Hey I tried this long formula and it worked VLOOKUP(IF(COUNTIF(B4,”*”&$H$4&”*”)<>0,$H$4,IF(COUNTIF(B4,”*”&$H$5&”*”)<>0,$H$5,IF(COUNTIF(B4,”*”&$H$6&”*”)<>0,$H$6,IF(COUNTIF(B4,”*”&$H$7&”*”)<>0,$H$7,IF(COUNTI………………..
my answer is
=VLOOKUP((TRIM(RIGHT((LEFT(B4,FIND(” x”,B4))),4))),$H$4:$I$9,2,0)
=IF(ISERROR(FIND(H$4,B4)),IF(ISERROR(FIND(H$5,B4)),IF(ISERROR(FIND(H$6,B4)),IF(ISERROR(FIND(H$7,B4)),IF(ISERROR(FIND(H$8,B4)),IF(ISERROR(FIND(H$9,B4)),0,250),150),120),75),60),50)
Please display the full formula
Hi Chandoo
Loved this post!
I find these days, I immediately default to using Boolean logic when dealing with array formulas – especially as the COUNTIF first argument “range” doesn’t accept arrays – BUT I did not know that the second argument can!
So here is my formula:
=INDEX(resRates,MATCH(1,COUNTIF(B4,”*”&resTypes&”*”),0))
Thanks for the great content
=LOOKUP(9.99999999999999E+307,SEARCH(resTypes,B4),resRates)
Being in the E-Commerce Industry, I use this awesome trick all the time to get the color of the products form there names from a list of colors.
Having said that the problem with this formula is that it would look up the keywords inside a word or any where in the text also for example the keyword “AM” would be looked up in the following sentence “NoAMing CM xxxx4607″ (just for eg.) but the correct answer should be CM instead of AM. To avoid this what I do is add a space before and after the keyword, which reduces the chances of this kind of errors.
Hi,
i used below formula & got the answers
=VLOOKUP(TRIM(RIGHT(LEFT(B4,FIND(“xxx”,B4,1)-2),3)),$H$4:$I$9,2,0)
Building the 2 char code:
=RIGHT(LEFT(B4,FIND(“x”,B4)-2),2)
find the wildcard code via vlookup:
=VLOOKUP(“*”&E4&”*”,$H$4:$I$9,2,0)
of course you can join them together. ( but it harder to understand )
E4 IN the previous is the 2 char code.
Haven’t seen my solution here yet:
{=SUM(IF(ISERROR(FIND(resTypes,B2)),0,resRates))}
This is an array formula. Enter it without the curly braces and hit Ctrl+Shift+Enter instead of Enter.
can some body explain me, =SEARCH(resTypes,B4) as it returns # VALUE! in my excel sheet
@Usman
It’s because the value in B4 can’t be found in the Range resTypes
This is either because B4 isn’t in the range or that the range isn’t where you think it is
You may also want to check that either B4 or the values in the range don’t have leading or trailing spaces
@Hui thanks, I guess u have answers to my questions
the thing is vijay posted this formula (above) =LOOKUP(16,SEARCH(resTypes,B4),resRates)
what I am trying is to understand is that how this formula works so I took out the SEARCH(resTypes,B4) part out to understand in pieces but it returns #VALUE! so I m confused, and one more thing is it necessary for the search formula refer to a named range or I can give simple range like H4:H9 ???
please help and thanks alot
@Usman
It depends on where the name ResTypes is referring to are you sure it’s where you expect?
Can you post the file?
I dont no how to post file ? as there is isnt any link for uploading file can i email u or some thing…..as I also want to send u the file for the Question on sum product that we have been discussing on another page.
@Usman
You can email me click on Hui… above or
http://chandoo.org/forums/topic/posting-a-sample-workbook
Well, here is my solution:
=SUMPRODUCT(COUNTIF(B4,”* “&resTypes&” *”),resRates)
by entering an array in COUNTIF function I get back an array of same size as resType consisting of 1s and 0s. So, I used that array to multiply the billing rate array using SUMPRODUCT. Brilliant website, makes the complicated understandable.
=VLOOKUP(TRIM(RIGHT(MID(B4,1,FIND(” x”,B4,1)),4)),$G$4:$H$9,2,FALSE)
=IF(ISNUMBER(FIND(“CM”,B5)),$I$7,IF(ISNUMBER(FIND(“PM”,B5)),$I$5,IF(ISNUMBER(FIND(“FM”,B5)),$I$6,IF(ISNUMBER(FIND(“AM”,B5)),$I$4,IF(ISNUMBER(FIND(“RM”,B5)),$I$8,IF(ISNUMBER(FIND(“CXO”,B5)),$I$9,))))))
I’m partial to SUMIFS
Here is what I used:
=SUMIFS(resRates,resTypes,TRIM(RIGHT(LEFT(B4,FIND(” x”,B4)),4)))
=LOOKUP((IF(ISNUMBER(FIND(“CM”,B4,1)),”CM”,IF(ISNUMBER(FIND(“AM”,B4,1)),”AM”,IF(ISNUMBER(FIND(“CXO”,B4,1)),”CXO”,IF(ISNUMBER(FIND(“RM”,B4,1)),”RM”,IF(ISNUMBER(FIND(“PM”,B4,1)),”PM”,IF(ISNUMBER(FIND(“FM”,B4,1)),”FM”,”XX”))))))),resTypes,resRates)
Can someone summerise the good answres such as the shortes one ( minimum functions) or the simple one to understand or most dynamic model ? ( it is quiet a lot of time to check all the answers)