Thread: Matching and Sorting

Hi,

I have three columns of data (alpha-numeric part numbers). The first column (B) is the "new part number", the 2nd (I) is the "old" part number, and the 3rd (J) is again the "new" part number.

The 2nd and 3rd columns are matched, so the "old" part number in I1 matches the "new" part number in J1. Columns C-H contain data (pricing, etc) for the "new" part # that is in column A.

I would like to add a column and be able to use an "IF" statement to add the correct old part # to correspond to the new part # in column A. I can't just do a simple sort function, because there are a lot of "new" part #'s that do not have corresponding "old" part numbers.

The formula I have been playing around with is this one:

=IF(ISNUMBER(MATCH(B2,J:J,0)),I???,"NA")

I think this will work, if I know how to place the match's row number in place of the ???

Here is an oversimplified example:

Thanks! Paul's worked without modifications, khamilton, it didn't work for me, I didn't have time to mess with it. It may be because I am still running the old version.

Thanks again,