VLOOKUP
 

I have 2 spreadsheets of unequal rows (Spreadsheet 1 = 20,000 rows,
Spreadsheet 2 = 5,000 rows). Can I use IF, VLOOKUP, MATCH, or INDEX (or a
combination of the 4) to systematically go through each cell in the 1st
column of spreadsheet 1, find a match from the 1st column of spreadsheet 2,
then place a value that correlates with that match in the 2nd column of
spreadsheet 1. What would this formula look like? Any help would be
appreciated. Thanks.

Hi!

When you say "2 spreadsheets", does that mean 2 workbooks
or 2 worksheets in the same workbook?

For 2 worksheets in the same workbook:

Assume Sheet1 A1:A20000
Sheet2 A1:B5000

In Sheet1 B1 enter this formula:

=IF(ISNA(VLOOKUP(A1,Sheet2!A$1:B$5000,2,0)),"",VLO OKUP
(A1,Sheet2!A$1:B$5000,2,0))

Copy down as needed.

This will lookup the value from Sheet1 A1 in Sheet2
A1:A5000 and if a match is found will return the
corresponding value from Sheet2 B1:B5000. If no match is
found the formula will return "" which leaves the cell
blank. (not to be confused with EMPTY)

For 2 WORKBOOKS:

Basically it's the same except you need to include the
workbook (file) name:

=IF(ISNA(VLOOKUP(A1,[Book2]
Sheet1A$1:B$5000,2,0)),"",VLOOKUP(A1,[Book2]Sheet1!
A$1:B$5000,2,0))

Biff

-----Original Message-----
I have 2 spreadsheets of unequal rows (Spreadsheet 1 =
20,000 rows,
Spreadsheet 2 = 5,000 rows). Can I use IF, VLOOKUP,
MATCH, or INDEX (or a
combination of the 4) to systematically go through each
cell in the 1st
column of spreadsheet 1, find a match from the 1st column
of spreadsheet 2,
then place a value that correlates with that match in the
2nd column of
spreadsheet 1. What would this formula look like? Any
help would be
appreciated. Thanks.
.

That formula worked perfectly! Thank you! Thank you! Thank you!

"Biff" wrote:

Hi!

When you say "2 spreadsheets", does that mean 2 workbooks
or 2 worksheets in the same workbook?

For 2 worksheets in the same workbook:

Assume Sheet1 A1:A20000
Sheet2 A1:B5000

In Sheet1 B1 enter this formula:

=IF(ISNA(VLOOKUP(A1,Sheet2!A$1:B$5000,2,0)),"",VLO OKUP
(A1,Sheet2!A$1:B$5000,2,0))

Copy down as needed.

This will lookup the value from Sheet1 A1 in Sheet2
A1:A5000 and if a match is found will return the
corresponding value from Sheet2 B1:B5000. If no match is
found the formula will return "" which leaves the cell
blank. (not to be confused with EMPTY)

For 2 WORKBOOKS:

Basically it's the same except you need to include the
workbook (file) name:

=IF(ISNA(VLOOKUP(A1,[Book2]
Sheet1A$1:B$5000,2,0)),"",VLOOKUP(A1,[Book2]Sheet1!
A$1:B$5000,2,0))

Biff

-----Original Message-----
I have 2 spreadsheets of unequal rows (Spreadsheet 1 =
20,000 rows,
Spreadsheet 2 = 5,000 rows). Can I use IF, VLOOKUP,
MATCH, or INDEX (or a
combination of the 4) to systematically go through each
cell in the 1st
column of spreadsheet 1, find a match from the 1st column
of spreadsheet 2,
then place a value that correlates with that match in the
2nd column of
spreadsheet 1. What would this formula look like? Any
help would be
appreciated. Thanks.
.

You're welcome! Thanks for the feedback.

Biff

-----Original Message-----
That formula worked perfectly! Thank you! Thank you!
Thank you!

"Biff" wrote:

Hi!

When you say "2 spreadsheets", does that mean 2
workbooks
or 2 worksheets in the same workbook?

For 2 worksheets in the same workbook:

Assume Sheet1 A1:A20000
Sheet2 A1:B5000

In Sheet1 B1 enter this formula:

=IF(ISNA(VLOOKUP(A1,Sheet2!A$1:B$5000,2,0)),"",VLO OKUP
(A1,Sheet2!A$1:B$5000,2,0))

Copy down as needed.

This will lookup the value from Sheet1 A1 in Sheet2
A1:A5000 and if a match is found will return the
corresponding value from Sheet2 B1:B5000. If no match
is
found the formula will return "" which leaves the cell
blank. (not to be confused with EMPTY)

For 2 WORKBOOKS:

Basically it's the same except you need to include the
workbook (file) name:

=IF(ISNA(VLOOKUP(A1,[Book2]
Sheet1A$1:B$5000,2,0)),"",VLOOKUP(A1,[Book2]Sheet1!
A$1:B$5000,2,0))

Biff

-----Original Message-----
I have 2 spreadsheets of unequal rows (Spreadsheet 1 =
20,000 rows,
Spreadsheet 2 = 5,000 rows). Can I use IF, VLOOKUP,
MATCH, or INDEX (or a
combination of the 4) to systematically go through
each
cell in the 1st
column of spreadsheet 1, find a match from the 1st
column
of spreadsheet 2,
then place a value that correlates with that match in
the
2nd column of
spreadsheet 1. What would this formula look like? Any
help would be
appreciated. Thanks.
.


.