VLOOKUP

[Kim17740]

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.

 

[Biff]

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)),"",VLOOKUP
(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

 

[Kim17740]

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

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)),"",VLOOKUP
(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.
.

 

[Biff]

You're welcome! Thanks for the feedback.

Biff

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

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)),"",VLOOKUP
(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.
.

.