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

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">file name</td><td style=";">serial no.</td><td style=";">test val. 1</td><td style=";">test val 2</td><td style=";">test val. 3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">FT601272NF_T1270.xls</td><td style="text-align: right;;">10001</td><td style="text-align: right;;">40.3</td><td style="text-align: right;;">40.1</td><td style="text-align: right;;">40.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">FT601272NF_T1271.xls</td><td style="text-align: right;;">10001</td><td style="text-align: right;;">39.9</td><td style="text-align: right;;">39.5</td><td style="text-align: right;;">39.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">FT601272NF_T1272.xls</td><td style="text-align: right;;">10002</td><td style="text-align: right;;">42.1</td><td style="text-align: right;;">42.3</td><td style="text-align: right;;">42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">FT50NF.xls</td><td style="text-align: right;;">10003</td><td style="text-align: right;;">39.9</td><td style="text-align: right;;">39.5</td><td style="text-align: right;;">39.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">FT50NF_T1274.xls</td><td style="text-align: right;;">10006</td><td style="text-align: right;;">39.2</td><td style="text-align: right;;">39.2</td><td style="text-align: right;;">39.3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">product name</td><td style=";">serial no.</td><td style=";">test no.</td><td style=";">test val. 1</td><td style=";">test val 2</td><td style=";">test val. 3</td><td style=";">file name</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">FT60NF</td><td style="text-align: right;;">10001</td><td style="text-align: right;;">1270</td><td style="text-align: right;;">40.3</td><td style="text-align: right;;">40.1</td><td style="text-align: right;;">40.2</td><td style="background-color: #FFF2CC;;">FT601272NF_T1270.xls</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">FT60NF</td><td style="text-align: right;;">10001</td><td style="text-align: right;;">1271</td><td style="text-align: right;;">39.9</td><td style="text-align: right;;">39.5</td><td style="text-align: right;;">39.8</td><td style="background-color: #FFF2CC;;">FT601272NF_T1271.xls</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">FT60NF</td><td style="text-align: right;;">10002</td><td style="text-align: right;;">1272</td><td style="text-align: right;;">42.1</td><td style="text-align: right;;">42.3</td><td style="text-align: right;;">42</td><td style="background-color: #FFF2CC;;">FT601272NF_T1272.xls</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">FT50NF</td><td style="text-align: right;;">10003</td><td style="text-align: right;;">1273</td><td style="text-align: right;;">39.9</td><td style="text-align: right;;">39.5</td><td style="text-align: right;;">39.8</td><td style="background-color: #FFF2CC;;">FT50NF.xls</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">FT50NF</td><td style="text-align: right;;">10006</td><td style="text-align: right;;">1274</td><td style="text-align: right;;">39.2</td><td style="text-align: right;;">39.2</td><td style="text-align: right;;">39.3</td><td style="background-color: #FFF2CC;;">FT50NF_T1274.xls</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G9</th><td style="text-align:left">{=INDEX(<font color="Blue">$A$2:$A$6,MATCH(<font color="Red">B9&"/"&D9&"/"&E9&"/"&F9,$B$2:$B$6&"/"&$C$2:$C$6&"/"&$D$2:$D$6&"/"&$E$2:$E$6,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Iryme]

This worked fine, thank you!

 

[AlanY]

you're welcome