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.

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

    thanks

    rakesh gotad
    (india)

Leave a Reply to rakesh Cancel reply

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

*