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

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.

CCC789_9999 it the 9th serial in CCC789

Correct?

There are no serial > 9 available?

See the green cells in column E.

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

See the attached file.

Oeldere beat me with an answer... Here's an alternative. You can hide column B in my offering.

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

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.