Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
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. . |
#4
|
|||
|
|||
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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
Table Array in VLOOKUP Relies on Data Validation | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |