+ Reply to Thread
Results 1 to 5 of 5

Extract matches based on partial criteria in one column and exact match on another column

  1. #1
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Extract matches based on partial criteria in one column and exact match on another column

    Good day all,

    I have this formula which works good to extract partial matches in range B1:B100
    =INDEX($B$2:$B$19,AGGREGATE(15,6,(ROW($B$2:$B$19)-ROW($B$2)+1)/ISNUMBER(SEARCH($B$22,$B$2:$B$19)),ROW(A27)-ROW($A$26)))

    This formula works well in extracting partial criteria (ISNUMBER/SEARCH) defined on B22 from data on Column B.

    However, I'd like to include other criterias from Column C and D. Please refer to attached worksheet example. My formula can only assist me to get results A26:D36. However, I'd like to get results in A38:D40.

    Thanks
    Attached Files Attached Files
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract matches based on partial criteria in one column and exact match on another col

    Please try B27

    =IFERROR(INDEX($B$2:$B$19,AGGREGATE(15,6,(ROW($B$2:$B$19)-ROW($B$2)+1)/(LEFT($B$2:$B$19,4)=$B$22&"")/($C$2:$C$19=$B$23)/($D$2:$D$19=$B$24),ROWS(A$26:A26))),"")

    I change Search to Left, as Prefix should be on the left, for Search, 5219 could be anywhere.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Extract matches based on partial criteria in one column and exact match on another col

    Ver 2007 does not have Aggregate function.
    ARRAY function in A26 then drag across upto column D till Empty cells are seen
    Please Login or Register  to view this content.
    How ARRAY formula is entered

    Paste Formula in the cell.
    Press F2
    Hold Shift+ Ctrl Keys and hit Enter key.
    Now the formula is surrounded by {} by excel.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract matches based on partial criteria in one column and exact match on another col

    Another B27 for 2007 normal enter

    =IFERROR(INDEX(B:B,-1/(1/MOD(LARGE(INDEX((10^5-ROW($B$2:$B$20))*(LEFT($B$2:$B$20,4)=$B$22&"")*($C$2:$C$20=$B$23)*($D$2:$D$20=$B$24),),ROWS(B$27:B27)),-10^5))),"")

  5. #5
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Extract matches based on partial criteria in one column and exact match on another col

    Thanks everyone. @Bo_Ry formula on his last post worked like a charm without Array.

+ 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. [SOLVED] complex if with index & match formula based on criteria in a column to extract data
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2018, 01:06 PM
  2. Replies: 4
    Last Post: 05-31-2017, 09:41 AM
  3. Replies: 7
    Last Post: 12-08-2016, 04:35 PM
  4. To Sort exact and partial exact match for a single column.
    By Jagdev in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2014, 05:08 AM
  5. Find Some Matches Data In column With Criteria And Extract In ROW
    By a-a_m_ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-05-2012, 07:03 AM
  6. Replies: 5
    Last Post: 02-24-2011, 11:26 AM
  7. Replies: 4
    Last Post: 12-14-2009, 03:21 PM

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