Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have data in two worksheets that I would like to compare/link. To keep it
simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Ash,
Use a helper column Concatenate A and B in that column and use the concatenation of the search arguments in VLOOKUP "Ash" wrote in message ... I have data in two worksheets that I would like to compare/link. To keep it simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Copy this into C1 of Sheet1 (assuming no header row)
=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$1 0=A1)*(Sheet2!$B$1:$B$10=B1),0)) [Adjust 10 to the last row in your set] then press CTRL-SHIFT-ENTER and copy down till you want If sheets are in different files then you will have to add the filename before the sheetname in [] "Ash" wrote: I have data in two worksheets that I would like to compare/link. To keep it simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
for every row that exists in Wks 1, there is an match... in Wks 2.
OK, then you shouldn't have to be concerned with errors in not finding matching. Try this array formula** : =INDEX(Sheet2!C$1:C$4,MATCH(1,(Sheet2!A$1:A$4=A1)* (Sheet2!B$1:B$4=B1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Ash" wrote in message ... I have data in two worksheets that I would like to compare/link. To keep it simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Here is the formula I used:
=INDEX('Adding Funding Source'!$M$1:$M$337,MATCH(1,('Adding Funding Source'!$A$1:$A$337=B1)*('Adding Funding Source'!$N$1:$N$337=M1),0)) Here are the columns that Match: Column B in Wks 1 matches Column A in Wks 2 (Adding Funding Source) Column M in Wks 1 matches Column N in Wks 2 (Adding Funding Source) Column M in Wks 2 is the one that contains the data I would like to display in Wks 1. I entered the formula above based on the formula you provided, and after I pressed Clt-Shift-Enter, I got 0. The data that should have displayed was a text statement. Did I do something wrong? Thanks. "Sheeloo" wrote: Copy this into C1 of Sheet1 (assuming no header row) =INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$1 0=A1)*(Sheet2!$B$1:$B$10=B1),0)) [Adjust 10 to the last row in your set] then press CTRL-SHIFT-ENTER and copy down till you want If sheets are in different files then you will have to add the filename before the sheetname in [] "Ash" wrote: I have data in two worksheets that I would like to compare/link. To keep it simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Sheloo above gave the same recommendation. I made an attempt at the formula,
but it did not work. Please feel free to see my response to the post above. Thank you kindly for a response. "T. Valko" wrote: for every row that exists in Wks 1, there is an match... in Wks 2. OK, then you shouldn't have to be concerned with errors in not finding matching. Try this array formula** : =INDEX(Sheet2!C$1:C$4,MATCH(1,(Sheet2!A$1:A$4=A1)* (Sheet2!B$1:B$4=B1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Ash" wrote in message ... I have data in two worksheets that I would like to compare/link. To keep it simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Niek,
This may be the simplest/easiest solution for me to try. Thank you kindly for the response. "Niek Otten" wrote: Hi Ash, Use a helper column Concatenate A and B in that column and use the concatenation of the search arguments in VLOOKUP "Ash" wrote in message ... I have data in two worksheets that I would like to compare/link. To keep it simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
This returns the value in column M for the first match in columns A and N.
My bet is that you didn't notice that you had a match higher in your list. If you try this array formula: =MATCH(1,('Adding Funding Source'!$A$1:$A$337=B1) *('Adding Funding Source'!$N$1:$N$337=M1),0) (ctrl-shift-enter, still!) It'll show you the first row in that "adding funding source" worksheet that has a match in colunn A and N. Ash wrote: Here is the formula I used: =INDEX('Adding Funding Source'!$M$1:$M$337,MATCH(1,('Adding Funding Source'!$A$1:$A$337=B1)*('Adding Funding Source'!$N$1:$N$337=M1),0)) Here are the columns that Match: Column B in Wks 1 matches Column A in Wks 2 (Adding Funding Source) Column M in Wks 1 matches Column N in Wks 2 (Adding Funding Source) Column M in Wks 2 is the one that contains the data I would like to display in Wks 1. I entered the formula above based on the formula you provided, and after I pressed Clt-Shift-Enter, I got 0. The data that should have displayed was a text statement. Did I do something wrong? Thanks. "Sheeloo" wrote: Copy this into C1 of Sheet1 (assuming no header row) =INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$1 0=A1)*(Sheet2!$B$1:$B$10=B1),0)) [Adjust 10 to the last row in your set] then press CTRL-SHIFT-ENTER and copy down till you want If sheets are in different files then you will have to add the filename before the sheetname in [] "Ash" wrote: I have data in two worksheets that I would like to compare/link. To keep it simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash -- Dave Peterson |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You got it right.
See Dave's post for a possible reason. You will also get 0 if there is no match. To test add a row with the values you are testing with just to be sure. I always copy the cells to make sure everything is same.. "Ash" wrote: Here is the formula I used: =INDEX('Adding Funding Source'!$M$1:$M$337,MATCH(1,('Adding Funding Source'!$A$1:$A$337=B1)*('Adding Funding Source'!$N$1:$N$337=M1),0)) Here are the columns that Match: Column B in Wks 1 matches Column A in Wks 2 (Adding Funding Source) Column M in Wks 1 matches Column N in Wks 2 (Adding Funding Source) Column M in Wks 2 is the one that contains the data I would like to display in Wks 1. I entered the formula above based on the formula you provided, and after I pressed Clt-Shift-Enter, I got 0. The data that should have displayed was a text statement. Did I do something wrong? Thanks. "Sheeloo" wrote: Copy this into C1 of Sheet1 (assuming no header row) =INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$1 0=A1)*(Sheet2!$B$1:$B$10=B1),0)) [Adjust 10 to the last row in your set] then press CTRL-SHIFT-ENTER and copy down till you want If sheets are in different files then you will have to add the filename before the sheetname in [] "Ash" wrote: I have data in two worksheets that I would like to compare/link. To keep it simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You will also get 0 if there is no match.
No, you'll get a result of #N/A if there is no match. -- Biff Microsoft Excel MVP "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... You got it right. See Dave's post for a possible reason. You will also get 0 if there is no match. To test add a row with the values you are testing with just to be sure. I always copy the cells to make sure everything is same.. "Ash" wrote: Here is the formula I used: =INDEX('Adding Funding Source'!$M$1:$M$337,MATCH(1,('Adding Funding Source'!$A$1:$A$337=B1)*('Adding Funding Source'!$N$1:$N$337=M1),0)) Here are the columns that Match: Column B in Wks 1 matches Column A in Wks 2 (Adding Funding Source) Column M in Wks 1 matches Column N in Wks 2 (Adding Funding Source) Column M in Wks 2 is the one that contains the data I would like to display in Wks 1. I entered the formula above based on the formula you provided, and after I pressed Clt-Shift-Enter, I got 0. The data that should have displayed was a text statement. Did I do something wrong? Thanks. "Sheeloo" wrote: Copy this into C1 of Sheet1 (assuming no header row) =INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$1 0=A1)*(Sheet2!$B$1:$B$10=B1),0)) [Adjust 10 to the last row in your set] then press CTRL-SHIFT-ENTER and copy down till you want If sheets are in different files then you will have to add the filename before the sheetname in [] "Ash" wrote: I have data in two worksheets that I would like to compare/link. To keep it simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data. Columns A and B in both spreadsheets have like data. And for every row that exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2. Wks 1: Wks 2: A B A B C Story 2 Story 1 Red Story 1 Story 2 Blue Apple 3 Candy 1 Red Candy 1 Apple 3 Red I would like to populate Column C in Wks 1, with the corresponding data in Column C from Wks 2. So if, column A matches, then if column B matches, then C. If at any point it doesn't match, it should continue looking for the matching A/B combination to get C. I hope this makes sense. I think I'm really having a blonde moment. Thanks! Ash |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Replacing ISERROR Formula | Excel Worksheet Functions | |||
Adding ISERROR to formula | Excel Worksheet Functions | |||
Using iserror in formula | Excel Discussion (Misc queries) | |||
Using ISERROR to Solve #DIV/0 in a formula | Excel Worksheet Functions | |||
Int Iserror Len Mid Find formula | Excel Worksheet Functions |