Tom’s Tutorials For Excel: Match From List
The below picture shows one way to see if any member of a collection of smaller strings (what I call “Key Codes”) are found in a much longer Product Code string.
The formulas in the yellow cells search the entire Key Code range of A4:A8. If any Key Code is found to exist in the Long Product Code, a match is made and the particular Key Code is returned to indicate that match.
The formula in cell D4 and copied down to cell D25 is
=IF(ISNUMBER(MATCH(1,FIND($A$4:$A$8,C4)^0,0)),
INDEX($A$4:$A$8,MATCH(1,FIND($A$4:$A$8,C4)^0,0)),"").
This is an array formula, which goes on one line as all formulas do, but I broke it into two lines here so it can be read on this web page by all internet browsers.
Recall that for array formulas, you apply them to a cell with Ctrl+Shift+Enter, not just with Enter.
Dear Tom,
I am trying above formula but it is giving error, I using “FIND($A$4:$A$8,C4)^0” only this part, in this array only calculating first word of list and not going on second row.
example if there is “a to c” in 3 row the array only calculating only a and not going to other character b and c.
Your formula looks nothing like the one I posted which does work. Try my formula and test again.
Dear Tom,
First of all thanks for reply, yes it is not your complete formula, but a part of formula which i want to apply on some of my excel file.
If i got correct this formula work only if apply as whole not in part.?
Dear Tom,
I have one query in excel which I am not able to form a formula.
My company deals in Cable TV business and a part of revenue comes from various TV Channels as placement amount which is in the form of a yearly contract.
I have to prepare monthly income to be book, which I do almost manually.
Is it possible to apply a formula that split the revenue in duration of the contract and revenue should be equal in all months.
For example:
If a contract is from 04/15/2014 to 04/14/2015 for Rs. 1,20,000. Then monthly revenue should be from
April’2014 Rs.5,000 and from May’14 to March’14 10,000 per month and for April’15 is 5,000.
Basically I want to keep revenue equal monthly.
If I am not able to illustrate the situation i can give you the excel sheet with data.
Thanks in advance.
Sorry I have not been able to get back to you about this for a few days, as I am traveling. I have an idea, try posting your question on a =n Excel forum where I know many people are there to help. It is all free, and registration is easy:
http://www.mrexcel.com/forum/excel-questions/
Thanks sir, I have posted there
dear tom,
i am trying it so many time but its not done plzz suggest.
can u sent me this formula with example of excel sheet
thanks
rakesh gotad
(india)
Not clear what you are asking for. Please explain in more detail with an example and expected results.