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

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

2. ## 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. ## 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?

Here you go.

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

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

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

Another options...........using array formula

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

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

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

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