Hello everyone,
I have a challenging question. I am trying to lookup for a specific word / phrase inside an excel cell then return a value of 1,2 or 3.
I was able to use the Search function alone, but was not able to combine it with either a Lookup / Match / Index function.
Can someone please help. I have attached a sample file to illustrate my issue.
Thanks so much!
Lucas
This seems to do the trick =IFERROR(LOOKUP(9,999E+307;SEARCH($E$4:$E$15;A2);$F$4:$F$15);"-")
Thre is one difference on row 6. Working on it
Use this array formula
=IFERROR(INDEX($F$1:$F$12,MIN(IF(ISNUMBER(SEARCH($E$1:$E$12,A1)),ROW($E$1:$E$12),99))),".")
The problem is Pepe that row contains demerge and the list contains merge and demerge, so your Search has two matches, demerge is at character 47, merge at 49, and doing a lookup of a big number against it matches the 49, so it matches the merge not demerge, hence 2.Originally Posted by Pepe Le Mokko
Hi Bob and Pepe, thanks for your help!
Bob, I tried your formula but it does not work on my side.
Basically, if the cell is blank or if the cell has "This company has been delisted on dd/mm/yyyy", I'd like to get "."
If there is a reason for the delisting (e.g., in liquidation), I'd like to see the corresponding number which is "1" in this case.
I've attached a revised file to make it more clear. Thanks!
Try this..
I think it can't be done with excel formulas..
I think a vba code will do..
Don't forget to click *
Please Login or Register to view this content.
Last edited by Vikas_Gautam; 08-06-2014 at 12:06 PM.
It is an array formula, which means Control-Shift-Enter, not just Enter.
Here's the adjusted formula to your new layout
=IFERROR(INDEX($F$4:$F$15,MIN(IF(ISNUMBER(SEARCH($E$4:$E$15,A2)),ROW($E$4:$E$15)-ROW($E$4)+1,99))),".")
But so would your solution fail if a particular row contained e.g. "Takeover (hostile)" and the (sorted) table contained both "Takeover" and "Takeover (hostile)".
True, that is not an issue with the current choice of keywords, but in general the point stands...
Regards
Nobody suggested otherwise, indeed if you reverse the order of merger and demerger in the lookup table, mine fails and Pepe's works. I was just explaining to Pepe why his had failed, I did not expect to be critiqued for helping out.
But why is your comment to Pepe "helping out", yet mine to you a "critique"?
On the contrary, I was doing just as you did to Pepe: pointing out how your solution may fail, given a different dataset. Surely that is as much "helping" you in terms of your offered solution as you "helped" Pepe in terms of his?
One of the things I personally feel it is our obligation to try to do - as Forum Experts/Gurus - is to attempt to always give the most generalisable solution possible, and not, for example, one which works, but only by virtue of the current data being convenient so as to make it so.
A solution should take into account that that data may change, should it not?
Regards
Last edited by XOR LX; 08-06-2014 at 05:04 AM.
The difference is, I offered a solution, I don't recall seeing yours. So let's see your ... most generalisable solution
I'm sorry that my comment on your solution has obviously offended you. But I really don't think there's any need for such a sarcastic, argumentative response.
Besides, one should be free to offer comments/suggestions on other people's solutions without necessarily having to trump them.
Regards
=IFERROR(INDEX($F$4:$F$15,MATCH(TRUE,INDEX((COUNTIF($A2,"*"&$E$4:$E$15&"*")<>0),0),0)),".")
Try this formula in "C2" and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thanks so much Bob, Siva, Pepe, Vikas and XOR.
Bob's formula and as well as that of Siva works!
You guys are amazing. Thanks a million!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks