Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets
I have two spreadsheets, I want to see if the same information is on both
spreadsheets, but I have to compare two columns at the same time so I cant use VLOOK up in the convetionational way unless it can look up two cells. eg I have one spread sheet which we shall call sheet1 with information in Columns A,B,C,and D and I have another spreadsheet called sheet2 within information in coulmns A,B,C, and D. I want to know if a row in sheet1 has the same infomation in coulmn A and B as a same row in column A and B in sheet2. If it has please show column C in sheet1 in column E in sheet 2, please note the infomation must be in the same row. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Freddo wrote: I have two spreadsheets, I want to see if the same information is on both spreadsheets, but I have to compare two columns at the same time so I cant use VLOOK up in the convetionational way unless it can look up two cells. eg I have one spread sheet which we shall call sheet1 with information in Columns A,B,C,and D and I have another spreadsheet called sheet2 within information in coulmns A,B,C, and D. I want to know if a row in sheet1 has the same infomation in coulmn A and B as a same row in column A and B in sheet2. If it has please show column C in sheet1 in column E in sheet 2, please note the infomation must be in the same row. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets
=INDEX(Delivery Notes!$F$1:$F$500,MATCH(1,(F6=Delivery
Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))(one cell) This is the formula I put in but it does not seem to work please re-check, please advise where the curly brackets go they come up around everything, ie in front of the first equals and at the very end. "Dave Peterson" wrote: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Freddo wrote: I have two spreadsheets, I want to see if the same information is on both spreadsheets, but I have to compare two columns at the same time so I cant use VLOOK up in the convetionational way unless it can look up two cells. eg I have one spread sheet which we shall call sheet1 with information in Columns A,B,C,and D and I have another spreadsheet called sheet2 within information in coulmns A,B,C, and D. I want to know if a row in sheet1 has the same infomation in coulmn A and B as a same row in column A and B in sheet2. If it has please show column C in sheet1 in column E in sheet 2, please note the infomation must be in the same row. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two spreadsheets | Excel Worksheet Functions | |||
Comparing data from different spreadsheets???? | Excel Discussion (Misc queries) | |||
comparing 2 spreadsheets | Excel Discussion (Misc queries) | |||
Comparing Spreadsheets | Excel Worksheet Functions | |||
Please Help (Comparing Spreadsheets) | Excel Worksheet Functions |