Can Index & Match Formula find a paticular Part number

[vane0326]

Is there a formula could do this?

Say I enter a formula in Collumn (B2) and Is there a formula that it could reads if collumn (A2) '2207' of that workbook then it looks for that paticular part number in the other workbook collumn (A2:A2000) and when it finds it and grabs whatever value next to it say in collumn (C)

I tried to use this formula but it doesnot work.
=INDEX([mx20105.xls]Sheet1!$A$2:$A$222,MATCH(A2,[mx20105.xls]Sheet1!$C$2:$C$222,0))

 

[Aladin Akyurek]

Re: Can Index & Match Formula find a paticular Part numb

Is there a formula could do this?

Say I enter a formula in Collumn (B2) and Is there a formula that it could reads if collumn (A2) '2207' of that workbook then it looks for that paticular part number in the other workbook collumn (A2:A2000) and when it finds it and grabs whatever value next to it say in collumn (C)

I tried to use this formula but it doesnot work.
=INDEX([mx20105.xls]Sheet1!$A$2:$A$222,MATCH(A2,[mx20105.xls]Sheet1!$C$2:$C$222,0))

Your description is not aligned with the formula you invoke...

=INDEX([mx20105.xls]Sheet1!$C$2:$C$222,MATCH(A2,[mx20105.xls]Sheet1!$A$2:$A$222,0))

or simply:

=VLOOKUP(A2,[mx20105.xls]Sheet1!$A$2:$C$222,3,0)

 

[vane0326]

Re: Can Index & Match Formula find a paticular Part numb

Hey thanks! it works perfectly. But how can you get rid of the #/NA. Can we add it something to the formula that when it does match the cell would be blank?

=INDEX([mx20105.xls]Sheet1!$C$2:$C$222,MATCH(A2,[mx20105.xls]Sheet1!$A$2:$A$222,0))

 

[Aladin Akyurek]

Re: Can Index & Match Formula find a paticular Part numb

Hey thanks! it works perfectly. But how can you get rid of the #/NA. Can we add it something to the formula that when it does match the cell would be blank?

=INDEX([mx20105.xls]Sheet1!$C$2:$C$222,MATCH(A2,[mx20105.xls]Sheet1!$A$2:$A$222,0))

I guess you want a single formula...

=IF(ISNUMBER(MATCH(A2,[mx20105.xls]Sheet1!$A$2:$A$222,0)),INDEX([mx20105.xls]Sheet1!$C$2:$C$222,MATCH(A2,[mx20105.xls]Sheet1!$A$2:$A$222,0)),"")

 

[vane0326]

Re: Can Index & Match Formula find a paticular Part numb

Thank You so much it works perfectly!

Curios, Why does the ISNUMBER has to be used and what does it do?

Thanks!

 

[Aladin Akyurek]

Re: Can Index & Match Formula find a paticular Part numb

Thank You so much it works perfectly!

Curios, Why does the ISNUMBER has to be used and what does it do?

Thanks!

MATCH returns either a number (if it succeeds) or #N/A (if it fails). ISNUMBER applied to the result of MATCH will either return TRUE (when MATCH succeeds) or FALSE (when MATCH fails).