You certainly should be able to do this if I understand things correctly. By
spreadsheet, do you mean 2 different sheets in a single .xls file, or do you
mean 2 sheets in two different .xls files? In either case, it'll work, but
if in two .xls files, both workbooks will need to be open.
Also whether you're talking about 2 sheets in the same workbook or 2
different .xls files determines how the VLOOKUP() formula has to be built up.
First scenario: single .xls workbook, two worksheets involved.
worksheet named ss1 has the entries in columns A & B,
worksheet named ss2 has entries in column J that may match entries in column
A of ss1:
in column B on ss2, a formula like this would do (change B$12 to have the
last row number used on ss1 in columns A:B).
=VLOOKUP(J1,'ss1'!A$1:B$12,2,FALSE)
to inhibit #N/A errors displaying when no match is found, change it to this
=IF(ISNA(VLOOKUP(J1,'ss1'!A$1:B$12,2,FALSE)),"",VLOOKUP(J1,'ss1'!A$1:B$12,2,FALSE))
Second scenario: two workbooks (.xls files) ---
the one with the information in columns A & B is named Book3.xls and the
sheet name there is still ss1:
=VLOOKUP(J2,[Book3.xls]ss1!$A$1:$B$12,2,FALSE)
notice that the lookup array now contains both the name of the other .xls
file along with the sheet in it where the array is.
Same trick to inhibit #N/A errors
=IF(ISNA(VLOOKUP(J2,[Book3.xls]ss1!$A$1:$B$12,2,FALSE)),"",VLOOKUP(J2,[Book3.xls]ss1!$A$1:$B$12,2,FALSE))