Lookup Need Help fast

T

TXDalessandros

I have two spreadsheets that have the following in column A and B
Address TripCode
4137 BOARDMAN-CANFIELD RD AK003

I need to look up the value from SS 1 Column A(ADDRESS) in another
spreadsheet (the same value is in spreadsheet 2 column j) and return The
TripCode (SS 1 COL b from that address into SS2 COLUMN b IS THIS POSSIBLE
WITH THE VLOOKUP FUNCTION?

THANK YOU!!!
 
J

JLatham

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))
 
J

JLatham

Actually, this may work with the second workbook closed - does in 2007.
Somedays I remember things wrong.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top