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

Iryme

New Member
Joined
Dec 10, 2012
Messages
5
I have 2 tables containing some product information like this:

product nameserial no.test no.test val. 1test val 2test val. 3
FT60NF10001127040.340.140.2
FT60NF10001127139.939.539.8
FT60NF10002127242.142.342.0
FT50NF10003127339.939.539.8
FT50NF10006127439.239.239.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 nameserial no.test val. 1test val 2test val. 3
FT601272NF_T1270.xls1000140.340.140.2
FT601272NF_T1271.xls1000139.939.539.8
FT601272NF_T1272.xls1000242.142.342.0
FT50NF.xls1000339.939.539.8
FT50NF_T1274.xls1000639.239.239.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 ...
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this


Excel 2012
ABCDEFG
1file nameserial no.test val. 1test val 2test val. 3
2FT601272NF_T1270.xls1000140.340.140.2
3FT601272NF_T1271.xls1000139.939.539.8
4FT601272NF_T1272.xls1000242.142.342
5FT50NF.xls1000339.939.539.8
6FT50NF_T1274.xls1000639.239.239.3
7
8product nameserial no.test no.test val. 1test val 2test val. 3file name
9FT60NF10001127040.340.140.2FT601272NF_T1270.xls
10FT60NF10001127139.939.539.8FT601272NF_T1271.xls
11FT60NF10002127242.142.342FT601272NF_T1272.xls
12FT50NF10003127339.939.539.8FT50NF.xls
13FT50NF10006127439.239.239.3FT50NF_T1274.xls
Sheet7
Cell Formulas
RangeFormula
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.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,008
Messages
5,834,850
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top