Thread: Compare cells and importing matching data

I have 2 spreadsheets. Within the 2 speadsheets is a matching part number. ex (in sheet1 - C2504=69600R-16 and this matches sheet2 - A2=69600R-16.

Basically I want to import the matching data into excel.

In this occasion, I want to write a formula that will search from A2:A315 of sheet 2 to find its matching cell in sheet 1 and then copy the adjacent 5 cells into my master sheet1.

So when sheet1 C2504 = sheet2 A2, then paste the contents of sheet 2 B2:E2 into sheet1 IV2504:IY2504

Thank you in advance for your assistance,
Doc

Have a look at the VLOOKUP() function. It should be what you're after.

If you have any problems implementing it post back.

http://www.excelfunctions.net/ExcelVlookup.html

Cutter,

Thank you. Final formula looked like this.

=VLOOKUP( C2501, [One_Piece_Pushrod.xlsx]Pushrods!$A$2:$E$315, 2, FALSE)

It gave me a result of #N/A in a number of cells, so it was a little messy but paste special and find/replace did the deed. I could not think of how to integrate this into the IF function to use "" if there were no results.

Anyway this is going to be very useful over the next few weeks, the way it is.

Doc

To get rid of the #N/A results you would use this:

=IF(ISNA(VLOOKUP( C2501, [One_Piece_Pushrod.xlsx]Pushrods!$A$2:$E$315, 2, FALSE)),"",VLOOKUP( C2501, [One_Piece_Pushrod.xlsx]Pushrods!$A$2:$E$315, 2, FALSE))

Or you could use your original formula and implement a Conditional Format to 'white-out' the #N/A's with white font

Originally Posted by Cutter

To get rid of the #N/A results you would use this:

=IF(ISNA(VLOOKUP( C2501, [One_Piece_Pushrod.xlsx]Pushrods!$A$2:$E$315, 2, FALSE)),"",VLOOKUP( C2501, [One_Piece_Pushrod.xlsx]Pushrods!$A$2:$E$315, 2, FALSE))

Or you could use your original formula and implement a Conditional Format to 'white-out' the #N/A's with white font

Cutter,

Can you use the same command style for Value errors? ..... ISVALUE .......

Yep, change the ISNA to ISERROR.

But you might want to check the Trouble Shooting area at the bottom of the page for which I provided a link.

The ISNA() takes care of situations when the VLOOKUP() can't find a match but the ISERROR() will take care of all returned errors. This may not be a good thing because it could mask errors that you should be aware of so you can rectify a problem if it exists.

Ok. thnx
Doc