Search and If found return adjacent cell [Solved]

Hello all,

I am working on a macro to compare two separate workbooks for accuracy on our reporting. I previously was using a line formula as shown here after merging them onto the same workbook but separate sheets:

=IFERROR(INDEX('Data1'!$D:$D,MATCH($A178,'Data1'!$C:$C,0)),"")
=IFERROR(INDEX('Data2'!$D:$D,MATCH($A178,'Data2'!$N:$N,0)),"")

I then just drag the formula down to search each line item against the secondary database. It has been working this way but I started messing with the VBA and think I can get a much cleaner/simpler result. Also one that can be easily transferred to other employees as an add-on

Simply put, I have a two columns (A, B). Column A holds a special code associated with a box letter sequence in column B.
I uploaded a spreadsheet with this to help you understand.

How can I:

search using the contents of cell A2 against Sheet2 column A
if found then copy adjacent cell (column B) on Sheet2 to Sheet1 column C?
if not found then write "not found"
repeat using contents of cell A3, then A4, etc until end.

I really don't have the experience or knowledge to even get started on this and my Goggle searches have yielded nothing positive.

Hello,

formulas will always be more efficient than rolling your own with VBA. If you want to optimise performance, stick with the formulas.

cheers, teylyn

Originally Posted by jpullen88

Forgive my ignorance but why is this?

Because formulas use the super efficient internal calculation engine of Excel, where as VBA adds another processing layer on top. Also, in this example VBA is used to loop through all cells and uses a read and/or write operation for each cell. That is very inefficient and there are ways to write faster VBA. In addition, formulas will update when the precendent cells change, whereas the VBA routine will have to be repeated. This can be automated, of course, but effectively you're re-inventing the wheel and replacing Excel's built-in Tesla with a tricycle.