INDEX/MATCH or just VLOOKUP is the right formula for this task ?

[Iryme]

I have 2 tables containing some product information like this:

product name	serial no.	test no.	test val. 1	test val 2	test val. 3
FT60NF	10001	1270	40.3	40.1	40.2
FT60NF	10001	1271	39.9	39.5	39.8
FT60NF	10002	1272	42.1	42.3	42.0
FT50NF	10003	1273	39.9	39.5	39.8
FT50NF	10006	1274	39.2	39.2	39.3

<tbody>
</tbody>

The second sheet contains data extracted from test files that were made and contains some common data like test results and part of cell info:

file name	serial no.	test val. 1	test val 2	test val. 3
FT601272NF_T1270.xls	10001	40.3	40.1	40.2
FT601272NF_T1271.xls	10001	39.9	39.5	39.8
FT601272NF_T1272.xls	10002	42.1	42.3	42.0
FT50NF.xls	10003	39.9	39.5	39.8
FT50NF_T1274.xls	10006	39.2	39.2	39.3

<tbody>
</tbody>

I need to find the file name corresponding to the test number. I've tried vlookup returning the name from merging the test results and comparing, but there are cases where the test values are the same for different test. Also, some files doesn't have test number in name of the file, so i need something to eliminate the resulting filenames that contains test number for these cases.

Waiting for opinions ...

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E	F	G
1	file name	serial no.	test val. 1	test val 2	test val. 3
2	FT601272NF_T1270.xls	10001	40.3	40.1	40.2
3	FT601272NF_T1271.xls	10001	39.9	39.5	39.8
4	FT601272NF_T1272.xls	10002	42.1	42.3	42
5	FT50NF.xls	10003	39.9	39.5	39.8
6	FT50NF_T1274.xls	10006	39.2	39.2	39.3
7
8	product name	serial no.	test no.	test val. 1	test val 2	test val. 3	file name
9	FT60NF	10001	1270	40.3	40.1	40.2	FT601272NF_T1270.xls
10	FT60NF	10001	1271	39.9	39.5	39.8	FT601272NF_T1271.xls
11	FT60NF	10002	1272	42.1	42.3	42	FT601272NF_T1272.xls
12	FT50NF	10003	1273	39.9	39.5	39.8	FT50NF.xls
13	FT50NF	10006	1274	39.2	39.2	39.3	FT50NF_T1274.xls
Sheet7

Cell Formulas
Range	Formula
G9	{=INDEX($A$2:$A$6,MATCH(B9&"/"&D9&"/"&E9&"/"&F9,$B$2:$B$6&"/"&$C$2:$C$6&"/"&$D$2:$D$6&"/"&$E$2:$E$6,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Iryme]

This worked fine, thank you!

 

[AlanY]

you're welcome