Index/Match returning only the first value

[Tronno]

Hi, I am having a issue with Index/Match. I am trying to quality check some input. B/C is the correct data in my main sheet, but I need to verify that the input from another sheet is correct E/F. In column D i use the formula: INDEX(F:F;MATCH(E2;B:B;0), But when the part number have multiple serial numbers, it only returns the first serial number. I appreciate if someone can help with this. A NR B Part Number C Serial number D Result E Part Number F Serial number 1 2196729 010-001 010-001 2196729 010-001 2 2196728 U-17-02 U-17-02 2196728 U-17-02 3 2196727 U-12-01 U-12-01 2196727 U-12-01 4 2196727 U-16-01 U-12-01 2196727 U-16-01 5 2196726 099-01 099-01 2196726 099-01 6 2196725 977-01 977-01 2196725 977-01 7 2196725 088-01 977-01 2196725 088-01 8 2196724 536-01 536-01 2196724 536-01 9 2196723 1 1 2196723 1 10 2196723 2 1 2196723 2 <tbody> </tbody>

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[AhoyNC]

As long as columns E & F are sorted in the same order as B & C then this array formula should do what you want.

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C	D	E	F
1	NR	Part Number	Serial number	Result	Part Number	Serial number
2	1	2196729	010-001	010-001	2196729	010-001
3	2	2196728	U-17-02	U-17-02	2196728	U-17-02
4	3	2196727	U-12-01	U-12-01	2196727	U-12-01
5	4	2196727	U-16-01	U-16-01	2196727	U-16-01
6	5	2196726	099-01	099-01	2196726	099-01
7	6	2196725	977-01	977-01	2196725	977-01
8	7	2196725	088-01	088-01	2196725	088-01
9	8	2196724	536-01	536-01	2196724	536-01
10	9	2196723	1	1	2196723	1
11	10	2196723	2	2	2196723	2
Sheet

 

[Tronno]

Thank you AhoyNC! This worked like a charm!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.