+ Reply to Thread
Results 1 to 15 of 15

Using INDEX MATCH to return unique values for non-unique search term

  1. #1
    Registered User
    Join Date
    06-01-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    6

    Using INDEX MATCH to return unique values for non-unique search term

    Hi all,

    I've scoured the forums and the internet in general for a solution to my problem, but can't quite find what I need.

    I have a column containing part numbers and a column containing barcodes (the barcode data contains the part number and serial number seperated by "_"). The barcodes are in no particular order. I've written an INDEX MATCH formula that searches the barcode data for a part number, then populates another column with the serial number, as shown below:

    A B C
    1 Part # Serial Barcode
    2 AAA123 11223 CCC789_9999
    3 BBB456 123123 AAA123_11223
    4 CCC789 9999 BBB456_123123
    5 AAA123 11223 AAA123_22334
    6 AAA123 11223 AAA123_33445
    7 AAA123 11223 AAA123_44556

    The formula used in cell B2 is:

    Please Login or Register  to view this content.
    As you can see there is a problem. I have 4 duplicate parts (AAA123) but 4 unique barcodes, and the INDEX MATCH only returns the first instance of the barcode containing that part number.

    I've tried playing around with array functions like SMALL but I can't quite figure out how to get them working.

    I know this would be a lot easier using VBA but for various reasons I want to avoid using macros.

    Thank you in advance for you help!

    Rico
    Last edited by rico_suave; 06-02-2015 at 07:27 PM. Reason: Solved

  2. #2
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    516

    Re: Using INDEX MATCH to return unique values for non-unique search term

    Instead of rearranging the shattered barcodes,why can't we try merging Part number and serial numbers to get the barcodes?

    Assume the part number is in A1 and serial number is B1

    Formula: "=A1&"_"&B1"

  3. #3
    Registered User
    Join Date
    06-01-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: Using INDEX MATCH to return unique values for non-unique search term

    Hi Shareez,

    Apologies, my explanation might not have been very clear. I have the part numbers, and I have the barcode data, and I'm trying to extract the serial number from the barcode as I'm not provided with the serial number.

    Does that make sense?

  4. #4
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    516

    Re: Using INDEX MATCH to return unique values for non-unique search term

    Here you go.

    Please find the attached sheet.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-01-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: Using INDEX MATCH to return unique values for non-unique search term

    Hi Shareez,

    Thank you but that's not what I'm after.

    The list of part numbers and the order they're listed in is fixed, I cannot reorder them. Additionally, the order that the barcodes are scanned and subsequently copied into excel is in no particular order.

    This means it's not as simple as just separating the barcode into part number and serial number. I need to be able to search the list of barcodes based on the fixed list of part numbers, and then return a unique serial number.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,322

    Re: Using INDEX MATCH to return unique values for non-unique search term

    You get better help if you add the expected result, in an small excel file, without confidential information.

    After that post it on the forum.
    Notice my main language is not English.

  7. #7
    Registered User
    Join Date
    06-01-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: Using INDEX MATCH to return unique values for non-unique search term

    Ok I've attached an example below.

    In the file, the top table is what I'm getting, and the bottom table is what I want. The focus is the serials column.

    IndexMatch Duplicate Lookup Test.xlsx

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,322

    Re: Using INDEX MATCH to return unique values for non-unique search term

    CCC789_9999 it the 9th serial in CCC789

    Correct?

    There are no serial > 9 available?

  9. #9
    Registered User
    Join Date
    06-01-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: Using INDEX MATCH to return unique values for non-unique search term

    The serial number can be anything, I simply used 9999 as a placeholder

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,322

    Re: Using INDEX MATCH to return unique values for non-unique search term

    See the green cells in column E.

    I solved this with using helpcolumns and after that a VLookup.

    See the attached file.

  11. #11
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: Using INDEX MATCH to return unique values for non-unique search term

    Oeldere beat me with an answer... Here's an alternative. You can hide column B in my offering.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 06-02-2015 at 03:15 AM.
    Glenn



  12. #12
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,197

    Re: Using INDEX MATCH to return unique values for non-unique search term

    Another options...........using array formula
    Attached Files Attached Files

  13. #13
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: Using INDEX MATCH to return unique values for non-unique search term

    It just occurred to me that I don't need the helper as a separate column. One non-array formula, copied down...
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    06-01-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: Using INDEX MATCH to return unique values for non-unique search term

    Thank you all for your help! I can see what I was doing wrong with SMALLL() now, it is much appreciated.

  15. #15
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: Using INDEX MATCH to return unique values for non-unique search term

    Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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