Any Excel formula pros out there?

[joelogic]

I want to compare similar cells by row and column in two worksheets and if the values are the same then I want to pull data from that row and add it to the other worksheet. If someone can help me it would be appreciated and I will give further details and clarification.

Thanks.

Example workbook. If A1 of worksheet B is equal to A1 of worksheet A then copy E1/F1 from worksheet B to E1/F1 of worksheet A. And then repeat down each row until the end.

[Corbin Dallas]

You need to combine all the data in one workbook. You can have seperate worksheets, but two different workbooks causes issues.

Why not port the workbooks to Access and combine?

BTW, how much data are we talking about?

[DavidRSA]

Wouldn't this be achieved by a simple vlookup?

[OliveCookieMonsta]

=Vlookup(cell to match,other worksheet group of cells,column number with info, 2)

[joelogic]

Thanks for the fast responses. Simple vlookup. Simple to you maybe .

Sorry, I meant worksheets.
Data can range from 300 rows to 5000 rows.
When using vlookup how can I compare two cells that are almost the same.
Comparing A1 for worksheet A (100 Main St.) to A1 of worksheet B (100 Main St. #100)

[joelogic]

=Vlookup(cell to match,other worksheet group of cells,column number with info, 2)

Could you please give the example again but using the syntax?

Worksheet A
Worksheet B

Column A Worksheet A has the data I want to match to Column A Worksheet B.
Will the formula search Column A Worksheet B for a similar match? If there is a match then I want to copy Column E and F of that row on Worksheet A to the matched row on Worksheet B.

[thedonger]

Vlookup in NOT what you are looking for!

You need to do an if() formula.

If(condition,then,else)

=if(a=b,c,d)

[joelogic]

How would I write =if(A1 of worksheet A = anything between A1:A1000 of worksheet B, copy E1 and F1 to the match on worksheet B)?

[thedonger]

If you want to compare cells that are almost the same you might not be able to use a formula, might need to be done manual, but you can put all the data on one tab and sort so it can be done faster.

[joelogic]

If I had to do it manually it would make it not worth doing.

[Dr Rockso]

How would I write =if(A1 of worksheet A = anything between A1:A1000 of worksheet B, copy E1 and F1 to the match on worksheet B)?

You're going to want to nest an IF( ISERROR( and MATCH(

Something like

IF(ISERROR(MATCH(A1,A1:A1000,0)),"",E1)
IF(ISERROR(MATCH(A1,A1:A1000,0)),"",F1)

and put them in their respective columns. The way it's written above with the "" will leave a blank cell when there isn't a match. I don't remember the syntax for working in multiple workbooks, but I think you already have that answer to your question.

[wildcard]

This is one of those things that are easy to do.. but a real pain in the butt to explain since it can't be done with just a formula. More like 2 formulas and a sort.

"Vlookup" so that you have the data on a single sheet.
Compare the values using an "if" statement. Returns a certain value if they match or don't.
Sort the list so that whatever doesn't match floats to the top.
Done.