+ Reply to Thread
Results 1 to 9 of 9

Partial Match in a column that returns the cells to the right.

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2016
    Posts
    34

    Unhappy Partial Match in a column that returns the cells to the right.

    Hello Everyone,

    I have I list of unique identifiers in one worksheet. I would like to look up those identifiers in a separate sheet but the unique identifiers have suffixes. These are text unique identifiers. The Unique ID is six digits.

    When it has found the relevant data in the separate sheet, it needs to return the NINE cells to the right.

    I tried index and Match but it didn't seem to work.

    Can anyone help?

    Thanks

    L

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Partial Match in a column that returns the cells to the right.

    Hi,

    information to grab in I1:I100, long codes in A1:A100, six-digit code to match in J2



    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Hope it helps
    Last edited by canapone; 10-29-2014 at 10:35 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Partial Match in a column that returns the cells to the right.

    Hi again

    same results using:


    Please Login or Register  to view this content.
    but it's to be confirmed with control+shift+enter: array status.

    Cheers

  4. #4
    Registered User
    Join Date
    01-17-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: Partial Match in a column that returns the cells to the right.

    Hi canapone,

    Thanks for that but this returns the cell, not the cells to the right of it.

    Thanks

    L

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Partial Match in a column that returns the cells to the right.

    Hi again

    data in A2:I1000, I need to match J2 in A2:A1000

    To obtain first cell to the right from B2:B1000:


    Please Login or Register  to view this content.
    then copy to the right to get the list


    Hope it helps

  6. #6
    Registered User
    Join Date
    01-17-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: Partial Match in a column that returns the cells to the right.

    Hi there,

    That works brilliantly. Now if I want to drag down a table, is it possible to keep the same range each time (A2:I100) but for the cells to be J2, K2 L2 etc...?

    Thanks

    Jp

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Partial Match in a column that returns the cells to the right.

    Hi,


    =IFERROR(INDEX($A$2:$I$100,MATCH(J$2,INDEX(LEFT($A$2:$A$100,6),),0),COLUMN(B$1)),"")

    should do the trick, but I'd need a sample file to be sure about how to adjust absolute/relative references

    Regards

  8. #8
    Registered User
    Join Date
    01-17-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: Partial Match in a column that returns the cells to the right.

    Solved, forgot my dollar signs!!!

  9. #9
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Partial Match in a column that returns the cells to the right.

    Ciao,

    thanks for providing nice feedback: dollars are often very important.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Indicate a partial match from a column of data
    By webbmeister in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 06:34 AM
  2. [SOLVED] Finding a partial match within a column
    By kestefon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2013, 02:14 PM
  3. [SOLVED] Lookup partial match and enter that in another column same row
    By markDuffy in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-20-2013, 05:46 PM
  4. Match partial cells in a single EXCEL column?
    By kpratico in forum Excel General
    Replies: 7
    Last Post: 06-05-2012, 03:21 PM
  5. Compare two cells (Partial Match)
    By manny_cb in forum Excel General
    Replies: 1
    Last Post: 07-11-2008, 11:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1