Ladypep wrote...
....
This is actually in two separate workbooks, not worksheets.

Basically, here is a better run-down:

If the value in cell A1 in Book 1 is found in ANY cell in Book 2, then
return the value of the cell to the right of the cell that was located in
Book 2.

If you mean any cell in a particular column in a particular worksheet
in Book 2, then use VLOOKUP, e.g.,

=VLOOKUP(SomeCodeInBook1,'X:\Y\Z\[Book
2.xls]WorksheetNameHere'!$A$2:$B$1000,2,0)

This will locate the value SomeCodeInBook1 in column A in the given
worksheet in Book 2 and return the corresponding value in column B.
Modify file pathname, worksheet name and range as needed.