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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
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.

    Thanks in advance.

  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


    rakesh gotad

Leave a Reply

Your email address will not be published. Required fields are marked *