# Can Index & Match Formula find a paticular Part number

#### vane0326

##### Well-known Member
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))

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Re: Can Index & Match Formula find a paticular Part numb

vane0326 said:
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)

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))

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

vane0326 said:
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)),"")

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!

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

vane0326 said:
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).

Replies
6
Views
580
Replies
3
Views
327
Replies
3
Views
404
Replies
8
Views
433
Replies
3
Views
646

1,203,521
Messages
6,055,882
Members
444,830
Latest member

### 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.

### Which adblocker are you using?

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

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