# Tom’s Tutorials For Excel: Match From List

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.

###### 8 comments on “Tom’s Tutorials For Excel: Match From List”
1. Mukesh says:

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.

• Tom Urtis says:

Your formula looks nothing like the one I posted which does work. Try my formula and test again.

• Mukesh says:

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

2. Mukesh says:

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.

• Tom Urtis says:

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/

• Mukesh says:

Thanks sir, I have posted there

3. rakesh says:

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