Home 
Search 
Today's Posts 
#1




How do I connect fields from two spreadsheets
I have two spreadsheets.
the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
#2




You can use Vlookup.
Account numbers on Sheet1, A2 to A6000. Account numbers on Sheet2, A2 to A6000 Amounts on Sheet2, B2 to B6000. Enter this in B2 of Sheet1: =VLOOKUP(A2,Sheet2!$A$2:$B$6000,2,0) You can drag down to copy, or the easy way is to double click on the "fill handle" in the lower right corner of B2, which will copy the formula in B2 down Column B, as far as there is data in Column A.  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "The Good Deeds Team" wrote in message ... I have two spreadsheets. the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
#3




You can do this with VLookup formulas but there is a problem with that, that
you should be aware of. With 6000 rows of data and just 2 columns in Sheet2, you will need 6000 VLookup formulas. The problem is that the file will grow big in a hurry. If you have more than just those 2 columns, which I suspect you do, you will end up with a big file. If that is not a problem for you then I would say to go with the formulas. The alternative is to go with a VBA solution. Post back if you need more. HTH Otto "The Good Deeds Team" wrote in message ... I have two spreadsheets. the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
#4




You bring up a good point Otto.
The Index and Match combination is supposed to be much more efficient then Vlookup, although I have no idea how it compares to VBA. This should work faster then the Vlookup formula I first suggested: =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A $6000,0))  Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... You can do this with VLookup formulas but there is a problem with that, that you should be aware of. With 6000 rows of data and just 2 columns in Sheet2, you will need 6000 VLookup formulas. The problem is that the file will grow big in a hurry. If you have more than just those 2 columns, which I suspect you do, you will end up with a big file. If that is not a problem for you then I would say to go with the formulas. The alternative is to go with a VBA solution. Post back if you need more. HTH Otto "The Good Deeds Team" wrote in message ... I have two spreadsheets. the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
#5




RD
The VBA way may well be slower (looping through 6000 cells) but it doesn't increase the size of the file like all those formulas do. I usually refrain from using formulas when there are many (like 6000) formulas involved. Otto "RagDyer" wrote in message ... You bring up a good point Otto. The Index and Match combination is supposed to be much more efficient then Vlookup, although I have no idea how it compares to VBA. This should work faster then the Vlookup formula I first suggested: =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A $6000,0))  Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... You can do this with VLookup formulas but there is a problem with that, that you should be aware of. With 6000 rows of data and just 2 columns in Sheet2, you will need 6000 VLookup formulas. The problem is that the file will grow big in a hurry. If you have more than just those 2 columns, which I suspect you do, you will end up with a big file. If that is not a problem for you then I would say to go with the formulas. The alternative is to go with a VBA solution. Post back if you need more. HTH Otto "The Good Deeds Team" wrote in message ... I have two spreadsheets. the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
#6




FWIW,
A couple of years ago, after reading about some speed tests Aladin did, I switched a big WB database from a double (error checking) Vlookup formula to the Index and Match combination, It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes ! Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... RD The VBA way may well be slower (looping through 6000 cells) but it doesn't increase the size of the file like all those formulas do. I usually refrain from using formulas when there are many (like 6000) formulas involved. Otto "RagDyer" wrote in message ... You bring up a good point Otto. The Index and Match combination is supposed to be much more efficient then Vlookup, although I have no idea how it compares to VBA. This should work faster then the Vlookup formula I first suggested: =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A $6000,0))  Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... You can do this with VLookup formulas but there is a problem with that, that you should be aware of. With 6000 rows of data and just 2 columns in Sheet2, you will need 6000 VLookup formulas. The problem is that the file will grow big in a hurry. If you have more than just those 2 columns, which I suspect you do, you will end up with a big file. If that is not a problem for you then I would say to go with the formulas. The alternative is to go with a VBA solution. Post back if you need more. HTH Otto "The Good Deeds Team" wrote in message ... I have two spreadsheets. the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
#7




That is significant. I will remember that. Thanks. Otto
"RagDyer" wrote in message ... FWIW, A couple of years ago, after reading about some speed tests Aladin did, I switched a big WB database from a double (error checking) Vlookup formula to the Index and Match combination, It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes ! Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... RD The VBA way may well be slower (looping through 6000 cells) but it doesn't increase the size of the file like all those formulas do. I usually refrain from using formulas when there are many (like 6000) formulas involved. Otto "RagDyer" wrote in message ... You bring up a good point Otto. The Index and Match combination is supposed to be much more efficient then Vlookup, although I have no idea how it compares to VBA. This should work faster then the Vlookup formula I first suggested: =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A $6000,0))  Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... You can do this with VLookup formulas but there is a problem with that, that you should be aware of. With 6000 rows of data and just 2 columns in Sheet2, you will need 6000 VLookup formulas. The problem is that the file will grow big in a hurry. If you have more than just those 2 columns, which I suspect you do, you will end up with a big file. If that is not a problem for you then I would say to go with the formulas. The alternative is to go with a VBA solution. Post back if you need more. HTH Otto "The Good Deeds Team" wrote in message ... I have two spreadsheets. the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
#8




This is really cool and worked great  now I would like to extend the model.
The application is a budget model Budgets are stored in 140 separate worksheets, I thought about, having 140 tabs, but someone in the office said that would not work for them Your suggestion worked for me as long as the other spreahseet was open (as in the following example) =ROUND(INDEX('[10010 Budget Ofc of the President.xls]Sheet 1'!$F$1:$F$100, MATCH($A503,'[10010 Budget Ofc of the President.xls]Sheet 1'!$M$1:'[10010 Budget Ofc of the President.xls]Sheet 1'!$M$100,0))/3,2) However if I close the other spreadsheet, and the first spreadsheet as well, then open the first spreadsheet and say update from other spreadsheets, the fields gets the value REF# and the formula changes to this: =ROUND(INDEX('S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\[10010 Budget Ofc of the President.xls]Sheet 1'!$F$1:$F$100, MATCH($A503,'S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\[10010 Budget Ofc of the President.xls]Sheet 1'!$M$1:'S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\[10010 Budget Ofc of the President.xls]Sheet 1'!$M$100,0))/3,2) Even if this worked, I wanted to reduce the string S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\ to ..\oop that does not seem to work either Do you have any suggestions? "RagDyer" wrote: FWIW, A couple of years ago, after reading about some speed tests Aladin did, I switched a big WB database from a double (error checking) Vlookup formula to the Index and Match combination, It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes ! Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... RD The VBA way may well be slower (looping through 6000 cells) but it doesn't increase the size of the file like all those formulas do. I usually refrain from using formulas when there are many (like 6000) formulas involved. Otto "RagDyer" wrote in message ... You bring up a good point Otto. The Index and Match combination is supposed to be much more efficient then Vlookup, although I have no idea how it compares to VBA. This should work faster then the Vlookup formula I first suggested: =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A $6000,0))  Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... You can do this with VLookup formulas but there is a problem with that, that you should be aware of. With 6000 rows of data and just 2 columns in Sheet2, you will need 6000 VLookup formulas. The problem is that the file will grow big in a hurry. If you have more than just those 2 columns, which I suspect you do, you will end up with a big file. If that is not a problem for you then I would say to go with the formulas. The alternative is to go with a VBA solution. Post back if you need more. HTH Otto "The Good Deeds Team" wrote in message ... I have two spreadsheets. the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
#9




if the first table has the account number, but it does not exist in the
second table, the result is '#N/A', how can I make the result 0 (zero) "RagDyer" wrote: You bring up a good point Otto. The Index and Match combination is supposed to be much more efficient then Vlookup, although I have no idea how it compares to VBA. This should work faster then the Vlookup formula I first suggested: =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A $6000,0))  Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... You can do this with VLookup formulas but there is a problem with that, that you should be aware of. With 6000 rows of data and just 2 columns in Sheet2, you will need 6000 VLookup formulas. The problem is that the file will grow big in a hurry. If you have more than just those 2 columns, which I suspect you do, you will end up with a big file. If that is not a problem for you then I would say to go with the formulas. The alternative is to go with a VBA solution. Post back if you need more. HTH Otto "The Good Deeds Team" wrote in message ... I have two spreadsheets. the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
#10




Try this:
=IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$6000,0)),0,INDEX( Sheet2!$B$2:$B$6000,MATCH( A2,Sheet2!$A$2:$A$6000,0)))  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "The Good Deeds Team" wrote in message ... if the first table has the account number, but it does not exist in the second table, the result is '#N/A', how can I make the result 0 (zero) "RagDyer" wrote: You bring up a good point Otto. The Index and Match combination is supposed to be much more efficient then Vlookup, although I have no idea how it compares to VBA. This should work faster then the Vlookup formula I first suggested: =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A $6000,0))  Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "Otto Moehrbach" wrote in message ... You can do this with VLookup formulas but there is a problem with that, that you should be aware of. With 6000 rows of data and just 2 columns in Sheet2, you will need 6000 VLookup formulas. The problem is that the file will grow big in a hurry. If you have more than just those 2 columns, which I suspect you do, you will end up with a big file. If that is not a problem for you then I would say to go with the formulas. The alternative is to go with a VBA solution. Post back if you need more. HTH Otto "The Good Deeds Team" wrote in message ... I have two spreadsheets. the first has a list of account numbers, for example 10 20 30 The second has a list of acount numbers and an amount, for example 10 100 20 150 30 175 How can I in the fisrt spreadsheet, use a formula to look in the second spreadsheet for the corresponding account number, example 20, and pull the correct value, example 150 withoutout coding each row in the first spreadsheet to look exactly at the specific row in the second spreadsheet For example, I can do this and it works =(second spreadsheet!$F$11) however I just want it to know where in the second spreadsheet column F the value 20 exists and pull 175 from colum G I don't want to have to tell the firts spreadsheet it is on row 11, I want it to look through coulmn F and find the value 20, which matches the value in the first spreadsheet, and then give me the amount from that row in the second spreadsheet, say column g this application is really 6000 accounts in the first spreadsheet, that need to match 6000 accounts in the second spreadsheet, and I don't want to code it row by row, simplely match the accopunt numbers from the two spreadsheets and give me a value on the corresponding row. 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Sorting Spreadsheet with Merged Fields  Excel Discussion (Misc queries)  
How do I get my actuarial spreadsheets to comply with SOx?  Excel Discussion (Misc queries)  
Informatica Connect to Excel workbook  Excel Discussion (Misc queries)  
filter on colorfilled fields  Excel Worksheet Functions  
Formulas stop calculating at random times when editing a few spreadsheets.  Excel Discussion (Misc queries) 