# Can you find that pattern? [Homework]

Posted on December 14th, 2012 in Excel Challenges - 184 comments

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.

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:

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.

Need clues?

Read wildcards in COUNTIF formula page to get some clues.

 Highlight best week & month in a trend chart [tutorials] Show hide list boxes using VBA
 Written by Chandoo Tags: array formulas, challenge, countif(), downloads, homework, INDEX(), Learn Excel, Microsoft Excel Formulas, patterns, text processing, wildcards in excel Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 184 Responses to “Can you find that pattern? [Homework]”

1. Vijay Sharma says:

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

• Dmitry says:

Seems like here might be an answer:

• Irvine R says:

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)

• Greg says:

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.

• Bryan says:

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.

• Jon says:

I love this one however it fails if some string appears in a word
=LOOKUP(2^15;SEARCH( esTypes & " ";B4);resRates)

• Matt says:

Then you can use this one...
=LOOKUP(2^15,SEARCH(resTypes & " xxx", B4 & " xxx"), resRates)

• Eric says:

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.

• Matt says:

Almost 🙂
=LOOKUP(2^15,SEARCH(” ” & resTypes & ” xxx”, ” ” & B4 & ” xxx”), resRates)

• Matt says:

Uuups, no you are right...

• Shakeel says:

in fact the best & right answer

• GB says:

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)

2. GFC says:

=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)

• Bitoubi says:

cant replace right(left()) with a stxt()

3. James lilg says:

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

4. Dmitry says:

What i've figured out is:
{=INDEX(resRates;SUM(NOT(ISERROR(SEARCH(resTypes;B4;1)))*ROW(resTypes))-3)}

5. Sebastien says:

Here is my solution (array formula):
INDEX(resRates,MATCH(0,FIND(resTypes,B4,1)*(\$J\$4:\$J\$9),0))

6. Chantty says:

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...

• Ramanan says:

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))

• Ramanan says:

Both required Ctrl+shift+Enter

7. Luke M says:

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))

• Kuldeep says:

Yes Luke You Do Love Very much of Sumproduct

• Harish says:

sumproduct is useful function which was developed by excel user like us...and idea apply by MS in excel 2007 as sumifs......:D

8. Till Kta says:

GFC variation :

=RECHERCHEV(SUBSTITUE(DROITE(GAUCHE(B4;CHERCHE("xx";B4)-2);3);" ";"");\$H\$3:\$I\$9;2;0)

9. Fernando says:

array formula
=SUM((IFERROR(SEARCH(resTypes,b4),0)<>0)*resRates)

10. Sajan says:

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.

11. Nick M. says:

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

12. Matthieu says:

=OFFSET(resRates,MAX((IFERROR(FIND(resTypes,B4,1),0)>1)*resIDs)-1,0,1,1)

13. Matthieu says:

=OFFSET(resRates,MAX((IFERROR(FIND(resTypes,B4,1),0)>1)*resIDs)-1,0,1,1)
Ctrl+Shift+Enter of course.

14. Harish says:

=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))))))

16. Colin says:

=INDEX(resRates,MATCH(TRIM(LEFT(RIGHT(B4,LEN(B4)-FIND(" x",B4)+4),3)),resTypes,0),1)

17. Harlan says:

=SUMPRODUCT(COUNTIF(B4,"*"&resTypes&"*")*resRates)

18. shaji says:

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) ...

19. Ananya Jena says:

=VLOOKUP(SUBSTITUTE(MID(B4,FIND("x",B4,1)-4,3)," ",""),\$H\$4:\$I\$9,2,0)

• Samtheman says:

Awesome nonfancy formula

20. Detlef says:

Two solutions.

=LOOKUP(2,1/COUNTIF(B4,"*"&resTypes&"*"),resRates)
=LOOKUP(9^9,SEARCH(resTypes,B4),resRates)

• cypher says:

=VLOOKUP(TRIM(MID(B4,FIND("x",B4,1)-4,3)),\$H\$4:\$I\$9,2,FALSE)

21. Squiggler says:

=SUMPRODUCT(1-ISERROR(FIND(resTypes,B4)),resRates)

• James says:

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.

22. Nicholas says:

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.

23. Matt says:

=SUMPRODUCT(--(TRIM(MID(B4,FIND("x",B4)-4,3))=(resTypes)),resRates)

24. D Blakewood says:

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)

25. Tony Strong says:

=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))))))))))))

26. peejay says:

very elegant!

27. Steve says:

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"))))))

28. Brian says:

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)

29. Walter says:

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.

30. Michael Pennington says:

=SUM(resRates*COUNTIF(B4,"*"&resTypes&"*"))

Array entered

• Somak Roy Choudhury says:

WOOOOW!!!!

• AJ says:

Mike,

LOVE IT, LOVE IT,,simplicity

• Tony Strong says:

Looks simples, but I get zero for value when I plug the formula into cell C4 and down. Anyone else has that problem?

• Mayur Naik says:

Even I am getting Zero when i plug the same formula in C4.
Can you assist?

• subburaj says:

31. Somak Roy Choudhury says:

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

32. Chandoo says:

Excellent answers everyone... Here is my solution.

{=INDEX(resRates,MAX(COUNTIF(B4,"*"&resTypes&"*")*(resIDs)))}

• Why not just this array-entered formula without the INDEX part?

=MAX(COUNTIF(B4,"*"&resTypes&"*")*resRates)

• Wainers says:

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.

• vinoth david says:

{=VLOOKUP("*CM*",H4:I9,2,0)}

• Gabriel says:

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.

33. Afzal S says:

=IF(COUNTIF(B4,"*M*"),VLOOKUP(MID(B4,FIND("M",B4)-1,2),\$H\$4:\$I\$8,2,0),\$I\$9)

34. shrivallabha says:

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

35. =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)

36. zur says:

SUMP RODUCT IS SIMPLEST

37. GPCHV Dutt says:

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)

38. Shakeel says:

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

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

• Shakeel says:

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

39. JP DAVY says:

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

40. Sanjeev Gupta says:

=VLOOKUP("*"&H4&"*",\$B\$3:\$D\$33,3,0)

41. =MAX(IFERROR((SEARCH("*"&resTypes&"*",B4))*1*(resRates),0))

42. pmsocho says:

=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.

43. Uri says:

Meanwhile Waiting

44. JP DAVY says:

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 ...

45. Quotenjunkie says:

=MAX(IFERROR((SEARCH(resTypes;B4)/SEARCH(resTypes;B4))*resRates;0))

46. Patnaik says:

Hi

using with array formula

=MAX(IFERROR(SEARCH(resTypes,B4)/SEARCH(resTypes,B4)*(resRates),0))

CTR+SHFT+ENTER

• Harish says:

Not working

47. Faseeh says:

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,

48. Faseeh says:

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)

49. Faseeh says:

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)

50. Senthilkumar_RM says:

=VLOOKUP(TRIM(MID(B4,FIND("x",B4)-4,3)),\$H\$4:\$I\$9,2,0)

51. oranus says:

{=SUMPRODUCT(IF(IFERROR(FIND(\$H\$4:\$H\$9;B4);0)>0;1;0);\$I\$4:\$I\$9)}

52. daniil says:

=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.

53. jp says:

I ended up with the following array formula

=SUMPRODUCT(IF(ISNUMBER(FIND(resTypes,B28))=TRUE,1,0),resRates)

54. delvillardennis says:

This will work:

=LOOKUP(2^10,SEARCH(resTypes,B4),resRates)

• subburaj says:

Request you to explain about 2^10

55. Udit says:

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.

56. MOHAMMED A. WASEEM says:

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)

57. MOHAMMED A. WASEEM says:

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)

58. prince says:

=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 🙁

59. =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)

60. Amritansh says:

=INDEX(resRates,MATCH(TRUE,IFERROR(FIND(resTypes,B5),0)<>0,0))

Ctrl+Shift+Enter for array formula

61. Gaurav says:

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.

62. Gaurav says:

=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.

63. Oli says:

Late to the party but here is what I came up with
=MAX(IFERROR(MATCH("*"&\$H\$4:\$H\$9&"*",B4,0),0)*resRates)

64. Ryan says:

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.

65. Mayur Naik says:

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)

66. Yogesh says:

=VLOOKUP(TRIM(RIGHT(LEFT(B4,FIND(" xxx",B4,1)),4)),\$H\$4:\$I\$9,2,FALSE)

67. Istiyak says:

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

68. Antonio says:

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).

69. Tom Dorsey says:

=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.

70. Sachin says:

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....................

=VLOOKUP((TRIM(RIGHT((LEFT(B4,FIND(" x",B4))),4))),\$H\$4:\$I\$9,2,0)

72. Bill Tong says:

=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)

• zur says:

73. Grant says:

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

74. Raj Kumar Kothari says:

=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.

75. Jitendra Jain says:

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)

76. Gabriel says:

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 )

77. Gabriel says:

E4 IN the previous is the 2 char code.

78. Andreas says:

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.

79. Usman says:

can some body explain me, =SEARCH(resTypes,B4) as it returns # VALUE! in my excel sheet

• Hui... says:

@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

80. Usman says:

@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 ???

• Hui says:

@Usman
It depends on where the name ResTypes is referring to are you sure it's where you expect?
Can you post the file?

81. Usman says:

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.

82. richard says:

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.

83. Aniket Thombare says:

=VLOOKUP(TRIM(RIGHT(MID(B4,1,FIND(" x",B4,1)),4)),\$G\$4:\$H\$9,2,FALSE)

84. Mehul says:

=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,))))))

85. Brent says:

I'm partial to SUMIFS

Here is what I used:

=SUMIFS(resRates,resTypes,TRIM(RIGHT(LEFT(B4,FIND(" x",B4)),4)))

86. Rakesh Patel says:

=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)

87. Gabriel says:

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)

88. El_Terrible says:

=IF(NOT(ISERROR(FIND("AM",B4))),VLOOKUP("AM",RES,2,FALSE),IF(NOT(ISERROR(FIND("PM",B4))),VLOOKUP("PM",RES,2,FALSE),IF(NOT(ISERROR(FIND("FM",B4))),VLOOKUP("FM",RES,2,FALSE),IF(NOT(ISERROR(FIND("CM",B4))),VLOOKUP("CM",RES,2,FALSE),IF(NOT(ISERROR(FIND("RM",B4))),VLOOKUP("RM",RES,2,FALSE),IF(NOT(ISERROR(FIND("CXO",B4))),VLOOKUP("CXO",RES,2,FALSE),"NOTHING FOUND"))))))

This one took me awhile, but I finally came up with a solution. It's pretty lengthy, so I do apologize for that ;\.

89. Delson says:

=IF(RIGHT(MID(B4,1,FIND(" x",B4,1)-1),2)="XO",RIGHT(MID(B4,1,FIND(" x",B4,1)-1),3),RIGHT(MID(B4,1,FIND(" x",B4,1)-1),2))

90. Faisal Ali says:

=SUMIF(\$H\$4:\$H\$9,TRIM(MID(B4,FIND("xx",B4,1)-4,3)),\$I\$4:\$I\$9)

Maybe got lucky with the names !

91. Meeran ekkeri says:

=VLOOKUP(TRIM(MID(B4,FIND("xx",B4)-4,3)),\$H\$4:\$I\$9,2,FALSE)

92. Alexis David says:

Hi guys,

Here is what I just came up with (and I'm sure there is a way to get rid of the IFERROR)

=SUM((IFERROR(IF(FIND(resTypes;B4)0;1);0)*resRates))
and Ctrl + Alt + Enter

93. Roger says:

Hello everybody.

To solve this problem I used this formula for every cell in Billing Rate column.

=VLOOKUP(
IF(COUNTIF(B4,"*"&\$H\$4&"*"),1,
IF(COUNTIF(B4,"*"&\$H\$5&"*"),2,
IF(COUNTIF(B4,"*"&\$H\$6&"*"),3,
IF(COUNTIF(B4,"*"&\$H\$7&"*"),4,
IF(COUNTIF(B4,"*"&\$H\$8&"*"),5,
IF(COUNTIF(B4,"*"&\$H\$9&"*"),6,0)))))),
\$G\$4:\$I\$9,3)

I worked for me.

Thanks.

94. Roger says:

Hello everybody.

To solve this problem I used this formula for every cell in Billing Rate column.

=VLOOKUP(MATCH(1,COUNTIF(B4,"*"&resTypes&"*"),0),\$G\$4:\$I\$9,3)

Take care to enter the formula in cell C4 and finish with Shift+Ctrl+Enter because is a formula array. Then copy it to range C5:C33.

Thanks.

95. ALPESH says:

G H
particular amt
NOTHING CM 120
FEW MC 50

partricular amt
CM( J9) (?) 120
formula : =VLOOKUP("*"&J9&"*",\$G\$9:\$H\$10,2,FALSE)

96. David M. says:

=SUMPRODUCT(--(COUNTIF(\$B4,"*"&resTypes&"*")=1),resRates)

memory efficient array without ctrl+shift+enter.

97. Paul S. says:

Assuming the data in this table will have the following consistencies based on the data already there.
1. Assume that the last segment starts with at least 2 x's.
2. Assume there will be a space before the lookup code we want to use.
3. Assume code will always be 2 or 3 characters long.
For those of us not as proficient with concise formulas, I built the following formula step by step in cheater cells first (much easier to build it and follow it along), and then put them all together into the one cell. The formula I use is longer than many, but building it piece by piece, it may be easier for those of use who are not as "awesome" in Excel. It is all based on the 3 assumptions above and searching for key characters to find the lengths of the different segments we want to know so that we can use the mid formula.
The following is to get the Code to look up.
=MID(B4,SEARCH(" ",B4,SEARCH(" xx",B4,1)-6)+1,SEARCH(" xx",B4,1)-(SEARCH(" ",B4,SEARCH(" xx",B4,1)-6)+1))

Explanation:
=MID(B4, --- Want to take the middle portion of B4 for the code.

SEARCH(" ",B4,SEARCH(" xx",B4,1)-6)+1 -- Since the " xx" seems the easiest to find, the later portion looks for the " xx", and backs up 6 characters as our starting point to find the space before the 2 or 3 character code. Once you find the space before the code, you need to add one space to get the first character of the code.

SEARCH(" xx",B4,1)-(SEARCH(" ",B4,SEARCH(" xx",B4,1)-6)+1))
-- The first part of this gets the length of where the " xx" begins or where the code ends. The later part gets the length of where the code begins as explained in the item above. Subtracting the two, you get the length you want for the length of the Mid function.

The above gets the code value. You then add it to the vlookup to get the final formula which should be placed into cell C4 and copied down.
=VLOOKUP(MID(B4,SEARCH(" ",B4,SEARCH(" xx",B4,1)-6)+1,SEARCH(" xx",B4,1)-(SEARCH(" ",B4,SEARCH(" xx",B4,1)-6)+1)),\$H\$4:\$I\$9,2,FALSE)

I hope people can follow this. Trying to explain something like this in a post is a little difficult without the use of color coding, etc. I know this seems long, but building it step by step, it isn't too bad. The main thing is that it is using more "normal" functions for those of us who are not as "awesome" in Excel.

98. Mukesh says:

kindly explain me how to do this......

99. M. A. Waseem says:

=VLOOKUP(TRIM(CONCATENATE(MID(B4,FIND("x",B4,1)-1-3,1),MID(B4,FIND("x",B4,1)-1-2,1),MID(B4,FIND("x",B4,1)-1-1,1))),\$H\$4:\$I\$9,2,0)

This is my second response to this and this is far smaller compared to my earlier formula

100. Bibek Saha says:

Try this formula, It is working on my side:
=INDEX(\$J\$32:\$K\$37,MATCH(TRIM(MID(B32,FIND("xx",B32)-4,3)),\$J\$32:\$J\$37,0),2)

101. Chus says:

I guess the easier approach woul be sumproduct

=SUMPRODUCT(COUNT.IF(A1,"*"&resTypes&"*")*resRates)

But this one is easy aswell

=INDEX(resRates,MATCH(MID(A1,FIND("x",A1)-2,2),resTypes,0))

102. Danail says:

I'm getting on this bit late but my solution for getting the billing rate is:
=INDEX(resRates,MATCH(B4,"*"&resTypes&"*",1),1)

• Danail says:

woops, sorry - this was a mistake

103. Danail says:

This is my solution:
=INDEX(resRates,MATCH(50,FIND(resTypes,B4),1),1)

The constant "50" here depends on the length of the strings - here it is short and that's why 50.

104. Dylan says:

=VLOOKUP(IFERROR(MID(B4,FIND("M",B4)-1,2),"CXO"),\$H\$4:\$I\$9,2,FALSE)

105. vidushi says:

Why we have used max function in this formula :

=INDEX(resRates,MAX(COUNTIF(B4,"*"&resTypes&"*")*(resIDs)))

106. Francis says:

=VLOOKUP(TRIM(MID(B4,FIND("x",B4)-4,4)),\$H\$4:\$I\$9,2,0)

107. Ashok Sindkar says:

I am using Excel-2013 and it is very simple with it. I copied this all to new sheet. Then inserted one column after B column. Using flash fill CM,CXO,RM can be easily extracted in new column. After that required output can get easily using simple Vlookup formula.
=VLOOKUP(C4,\$I\$4:\$J\$9,2,FALSE).
Thanks.

108. faresar says:

Well i used the functions that i know and came up with nested if conditions. It's long i know but it works

=VLOOKUP(IF(ISNUMBER(SEARCH(\$H\$4,B4)),MID(B4,SEARCH(\$H\$4,B4),LEN(\$H\$4)),IF(ISNUMBER(SEARCH(\$H\$5,B4)),MID(B4,SEARCH(\$H\$5,B4),LEN(\$H\$5)),IF(ISNUMBER(SEARCH(\$H\$6,B4)),MID(B4,SEARCH(\$H\$6,B4),LEN(\$H\$6)),IF(ISNUMBER(SEARCH(\$H\$7,B4)),MID(B4,SEARCH(\$H\$7,B4),LEN(\$H\$7)),IF(ISNUMBER(SEARCH(\$H\$8,B4)),MID(B4,SEARCH(\$H\$8,B4),LEN(\$H\$8)),IF(ISNUMBER(SEARCH(\$H\$9,B4)),MID(B4,SEARCH(\$H\$9,B4),LEN(\$H\$9)),0)))))),\$H\$4:\$I\$9,2,0)

109. Elkhan says:

formula below, or any analogue of it quoted here, is ok unless you have a reference character (like "x" in this case). otherwise any repetition of ResTypes characters will return wrong answer. interestingly different formulas gives wrong answers in different cases depending on where and what kind of repetition occurs in Res Desc text string.

=VLOOKUP(TRIM(LEFT(RIGHT(B4,(LEN(B4)-FIND("x",B4)+5)),4)),\$H\$4:\$I\$9,2,FALSE)

110. Elkhan says:

In my previous post it should be "until you have a reference character", of course. Sorry for my bad English

111. lokesh says:

=VLOOKUP(IFERROR(MID(B4,SEARCH(" ?M",B4)+1,2),"CXO"),\$H\$4:\$I\$9,2,0)

112. Sam says:

=VLOOKUP(INDEX(resTypes,MATCH(1,COUNTIF(B4,"*"&resTypes&"*"),0)),\$H\$4:\$I\$9,2,0)

• Sam says:

Forgot to mention: use ctrl + shift + enter

113. Bitoubi says:

=VLOOKUP(TRIM(MID(B4,SEARCH("xxx",B4)-4,4)),\$H\$4:\$I\$9,2,0)

114. Haz says:

{=INDEX(resRates,MATCH(1,MATCH("* "&resTypes&" x*",B4,0),0))}

The " x*" part is there to find the correct resource, so having "FM Radio" in the description doesn't bring the wrong result.

115. Max says:

I split the text of that one column into several ones (seperating by spaces). Then I wrote this formula:

=IF(A2=\$P\$2;50;IF(A2=\$P\$3;60;IF(A2=\$P\$4;75;IF(A2=\$P\$5;120;IF(A2=\$P\$6;150;IF(A2=\$P\$7;250;0))))))

And pulled it over 5 columns to the right and all the way down. Last thing was taking the sums of of those 5 columns with the formula.

The P column has the legend.

116. Shibu Alex says:

Hi All,

This is formula which is giving correct answer for me 🙂

=COUNTIF(B4,CONCATENATE("*",\$H\$4,"*"))*\$I\$4+COUNTIF(B4,CONCATENATE("*",\$H\$5,"*"))*\$I\$5+COUNTIF(B4,CONCATENATE("*",\$H\$6,"*"))*\$I\$6+COUNTIF(B4,CONCATENATE("*",\$H\$7,"*"))*\$I\$7+COUNTIF(B4,CONCATENATE("*",\$H\$8,"*"))*\$I\$8+COUNTIF(B4,CONCATENATE("*",\$H\$9,"*"))*\$I\$9

117. subburaj says:

=IFERROR(VLOOKUP(MID(B4,FIND(\$H\$4,B4),2),\$H\$3:\$I\$9,2,0),IFERROR(VLOOKUP(MID(B4,FIND(\$H\$5,B4),2),\$H\$3:\$I\$9,2,0),IFERROR(VLOOKUP(MID(B4,FIND(\$H\$6,B4),2),\$H\$3:\$I\$9,2,0),IFERROR(VLOOKUP(MID(B4,FIND(\$H\$7,B4),2),\$H\$3:\$I\$9,2,0),IFERROR(VLOOKUP(MID(B4,FIND(\$H\$8,B4),2),\$H\$3:\$I\$9,2,0),IFERROR(VLOOKUP(MID(B4,FIND(\$H\$9,B4),3),\$H\$3:\$I\$9,2,0),0))))))

118. Dan says:

=SUM(COUNTIF(B4,"=*"&\$H\$4&"*")*\$I\$4,COUNTIF(B4,"=*"&\$H\$5&"*")*\$I\$5,COUNTIF(B4,"=*"&\$H\$6&"*")*\$I\$6,COUNTIF(B4,"=*"&\$H\$7&"*")*\$I\$7,COUNTIF(B4,"=*"&\$H\$8&"*")*\$I\$8,COUNTIF(B4,"=*"&\$H\$9&"*")*\$I\$9)

119. Sumit says:

Below is my solution

120. Sumit says:

Below is my solution:

VLOOKUP(TRIM(MID(B4,(FIND(" xx",B4)-3), 3)),\$H\$4:\$I\$9,2,0)

• Tim says:

The formula works. check mine below

121. Tim says:

Write the following formula in cell C4 and copy to the rest of the cells

=VLOOKUP(TRIM(RIGHT(LEFT(B4,FIND("xx",B4)-2),3)),\$H\$4:\$I\$9,2,FALSE)

122. Umang says:

Using simple excel functions-

=VLOOKUP(TRIM(RIGHT(TRIM(MID(B3,1,FIND(" x",B3)-1)),3)),\$H\$3:\$I\$8,2,0)

123. Agung Karjono says:

This is my solution

=LOOKUP(LEN(B4);FIND(\$H\$4:\$H\$9;B4;1);\$I\$4:\$I\$9)

124. VândaloIT says:

Hi all,

First of all I want you know this is the first time I see these challenges, so answering in 2015 a 2012 question !?!?!?! "Where you have been, you ask".

Never mind, straight to my solution:

=sum(if(isnumber(find(restype,b8));resrates)) ending with ctrl+shif+enter as it is an array formula.

Vândalo.

125. Philip Stevenson says:

My answer (a little bit long)

=VLOOKUP(IFERROR(IF(FIND(\$H\$4,B4,1)>0,1,0),IFERROR(IF(FIND(\$H\$5,B4,1)>0,2,0),IFERROR(IF(FIND(\$H\$6,B4,1)>0,3,0),IFERROR(IF(FIND(\$H\$7,B4,1)>0,4,0),IFERROR(IF(FIND(\$H\$8,B4,1)>0,5,0),IFERROR(IF(FIND(\$H\$9,B4,1)>0,6,0),"")))))),\$G\$4:\$I\$9,3,0)

126. Abhay says:

use this formula

=VLOOKUP("*"&MID(B4,FIND("x",B4)-3,2)&"*",\$H\$4:\$I\$9,2,FALSE)

127. Vândalo says:

It could seem silly among you excel gurus, but this was the first approach that crossed my mind.

CTRL+SHIFT+ENTER

=INDEX(resRates;MAX(--ISNUMBER(FIND(resTypes;B4))*{1;2;3;4;5;6}))

Vândalo

P.S. - I didn't red all the posts so I'm not aware if someone already posted an answer like this one.

128. surya prakash says:

=VLOOKUP((TRIM(RIGHT(LEFT(\$B4,FIND(" xxx",\$B4)-1),3))),H:I,2,FALSE)

129. Amit says:

=VLOOKUP(TRIM(MID(B4,FIND("x",B4)-4,3)),\$H\$4:\$I\$9,2,0)

130. Ata says:

=INDEX(resRates,SUMPRODUCT(resIDs,COUNTIF(B4,"*"&resTypes&"*")))

131. Vinay says:

Hi ,

I was using formula --> =VLOOKUP("*"&B4&"*",H4:I9,2,0) but this does not work and gives error.

Could someone please explain why it does not return the required output.

Thanks,

132. mukesh says:

=INDEX(resRates,MATCH(TRIM(LEFT(TRIM(SUBSTITUTE(B4,LEFT(B4,FIND(" x",B4,1)-4),"")),3)),\$H\$4:\$H\$9,0))

133. mukesh says:

=INDEX(\$I\$4:\$I\$9,MATCH(TRIM(MID(B4,IFERROR(FIND("gs",B4,1)+3,FIND("g ",B4,1)+2),3)),\$H\$4:\$H\$9,0))

134. mukesh says:

=INDEX(\$I\$4:\$I\$9,MATCH(TRIM(MID(B4,FIND(" x",B4,1)-3,3)),\$H\$4:\$H\$9,0))

135. mukesh says:

IFERROR(VLOOKUP(MID(B4,IFERROR(FIND(CHAR(77),B4,1),250)-1,2),\$H\$4:\$I\$9,2,FALSE),250)

136. mukesh says:

=VLOOKUP(TRIM(RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE(B4,"x",""),RIGHT(SUBSTITUTE(B4,"x",""),5),"")),3)),\$H\$4:\$I\$9,2,FALSE)

137. Mohammed says:

=IFERROR(VLOOKUP(MID(B4,SEARCH("xx",B4)-3,2),H\$3:I\$9,2,0),VLOOKUP(MID(B4,SEARCH("xx",B4)-4,3),H\$3:I\$9,2,0))

138. Nitya Nand Singh says:

=VLOOKUP(TRIM(MID(B4,FIND("xxx",B4)-4,3)),\$H\$4:\$I\$10,2,0)

139. tushar says:

=INDEX(\$H\$4:\$I\$9,MATCH(TRIM(MID(SUBSTITUTE(B4,"things","thing"),SEARCH("g",SUBSTITUTE(B4,"things","thing"))+1,4)),resTypes,0),2)

140. Ajay Kumar says:

=IFERROR(INDEX(resRates,MATCH(TRIM(RIGHT(LEFT(B4,SEARCH(" xx",B4)),4)),resTypes,0)),"Something went wrong !")

141. Rojo Loco says:

I like to use isnumber(search(indirect(....... to enable dynamic changing of the search results and then return a defined dynamic result (such as the cell being searched or the term searched for). This enables changing of the search term without changing the formula. In this case the search term is then used for a simple index match.

1 row of formulas:
Resource Description am pm fm cm rm cxo Billing Code Billing Rate Correct Answers
Nothing CM xxxx4607 =IF(ISNUMBER(SEARCH(INDIRECT("\$C\$3"),\$B4)),\$C\$3,"") =IF(ISNUMBER(SEARCH(INDIRECT("\$d\$3"),\$B4)),\$D\$3,"") =IF(ISNUMBER(SEARCH(INDIRECT("\$e\$3"),\$B4)),\$E\$3,"") =IF(ISNUMBER(SEARCH(INDIRECT("\$f\$3"),\$B4)),\$F\$3,"") =IF(ISNUMBER(SEARCH(INDIRECT("\$g\$3"),\$B4)),\$G\$3,"") =IF(ISNUMBER(SEARCH(INDIRECT("\$h\$3"),\$B4)),\$H\$3,"") =CONCATENATE(C4,D4,E4,F4,G4,H4) =INDEX(resRates,MATCH(J4,resTypes,0)) 120

Results appear as:
Resource Description am pm fm cm rm cxo Billing Code Billing Rate Correct Answers
Nothing CM xxxx4607 cm cm 120 120

142. Jayant says:

=VLOOKUP(IF(ISERR(FIND(\$H\$4,B4)),1,1)*IF(ISERR(FIND(\$H\$5,B4)),1,2)*IF(ISERR(FIND(\$H\$6,B4)),1,3)*IF(ISERR(FIND(\$H\$7,B4)),1,4)*IF(ISERR(FIND(\$H\$8,B4)),1,5)*IF(ISERR(FIND(\$H\$9,B4)),1,6),\$G\$4:\$I\$9,3,0)

143. kapil goyal says:

=IF(COUNTIF(B4,"*CXO*")=0,VLOOKUP(MID(B4,FIND("x",B4)-3,2),\$H\$3:I9,2,0),VLOOKUP(MID(B4,FIND("x",B4)-4,3),\$H\$3:I9,2,0))

144. Barman says:

nested if:
=IF(ISNUMBER(SEARCH(\$H\$4,B4)),\$I\$4,IF(ISNUMBER(SEARCH(\$H\$5,B4)),\$I\$5,IF(ISNUMBER(SEARCH(\$H\$6,B4)),\$I\$6,IF(ISNUMBER(SEARCH(\$H\$7,B4)),\$I\$7,IF(ISNUMBER(SEARCH(\$H\$8,B4)),\$I\$8,IF(ISNUMBER(SEARCH(\$H\$9,B4)),\$I\$9,NA))))))

145. Umakanth Kokkula says:

This worked for me:
=VLOOKUP(TRIM(RIGHT(TRIM(LEFT(B4,FIND("xxx",B4)-1)),3)),\$H\$4:\$I\$9,2,0)

Please correct me if i am wrong. 🙂

 Highlight best week & month in a trend chart [tutorials] Show hide list boxes using VBA