| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup 3 columns and return a result from another column
Hi,
I have a workbook (book 3) which consists of 8 columns of data. There are 3 columns of data which are very important. There is another workbook linked to book 3 which reads this entire data structure and performs decision making process. Basically what I want is to check the 3 columns for 3 different variables. if they match, then retun the number (in another column) of that row that matches all the variables Now i am looking for a function which can check the entries of the 3 columns and return the right corresponding number to that entry. For example, the three columns i have a Date, Customer, Defect Code First, it should read the date (month) , then the customer and then the defect code and then return the number of defects (which is in another column in book 3) I have inserted my version of code but obviously it doesn't recognize anything properly and work. =SUM(IF('[Book3.xls]Customer Returns (External)'!$A$3:$A$1200=$C$2, IF('[Book3.xls]Customer Returns (External)'!$C$3:$C$1200=C$3, IF('[Book3.xls]Customer Returns (External)'!$D$3:$D$1200=$A6, '[Book3.xls]Customer Returns (External)'!$J$3:$J$1200)))) A3:A1200 - List of all dates from Oct 2006 to Sept 2007 in Book 3 (column) C2 - Month cell from another workbook C3: C1200 - List of all customers in Book 3 (Column) C3 - Company cell from another workbook D3: D1200 - Defect Code column in Book 3 (Column) A6 - Defect Code Cell from another workbook J3: J1200 - Confirmed Defects (value that should be returned after satisfying these 3 conditions in book 3). I would appreciate if anyone can propose a better solution to this problem. Thanks in advance Swamy |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup 3 columns and return a result from another column
Try:
=INDEX('[Book3.xls]Customer Returns (External)'!$J$3:$J$1200,MATCH(1,($C$2='[Book3.xls]Customer Returns (External)'!$A$3:$A$1200)*($C$3='[Book3.xls]Customer Returns (External)'!$C$3:$C$1200)*($A$6='[Book3.xls]Customer Returns (External)'!$D$3:$D$1200),0)) Entered with Ctrl+Shift+Enter HTH " wrote: Hi, I have a workbook (book 3) which consists of 8 columns of data. There are 3 columns of data which are very important. There is another workbook linked to book 3 which reads this entire data structure and performs decision making process. Basically what I want is to check the 3 columns for 3 different variables. if they match, then retun the number (in another column) of that row that matches all the variables Now i am looking for a function which can check the entries of the 3 columns and return the right corresponding number to that entry. For example, the three columns i have a Date, Customer, Defect Code First, it should read the date (month) , then the customer and then the defect code and then return the number of defects (which is in another column in book 3) I have inserted my version of code but obviously it doesn't recognize anything properly and work. =SUM(IF('[Book3.xls]Customer Returns (External)'!$A$3:$A$1200=$C$2, IF('[Book3.xls]Customer Returns (External)'!$C$3:$C$1200=C$3, IF('[Book3.xls]Customer Returns (External)'!$D$3:$D$1200=$A6, '[Book3.xls]Customer Returns (External)'!$J$3:$J$1200)))) A3:A1200 - List of all dates from Oct 2006 to Sept 2007 in Book 3 (column) C2 - Month cell from another workbook C3: C1200 - List of all customers in Book 3 (Column) C3 - Company cell from another workbook D3: D1200 - Defect Code column in Book 3 (Column) A6 - Defect Code Cell from another workbook J3: J1200 - Confirmed Defects (value that should be returned after satisfying these 3 conditions in book 3). I would appreciate if anyone can propose a better solution to this problem. Thanks in advance Swamy |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup 3 columns and return a result from another column
Thank you for your attempt. It returns me "#NA" and I dont know why
this happens. I forgot to mention that Columns A, C and D are in text format and Column J (Number of defects) is in number format. Thanks for your help Swamy On Feb 21, 5:32 am, Toppers wrote: Try: =INDEX('[Book3.xls]Customer Returns (External)'!$J$3:$J$1200,MATCH(1,($C$2='[Book3.xls]Customer Returns (External)'!$A$3:$A$1200)*($C$3='[Book3.xls]Customer Returns (External)'!$C$3:$C$1200)*($A$6='[Book3.xls]Customer Returns (External)'!$D$3:$D$1200),0)) Entered with Ctrl+Shift+Enter HTH " wrote: Hi, I have a workbook (book 3) which consists of 8 columns of data. There are 3 columns of data which are very important. There is another workbook linked to book 3 which reads this entire data structure and performs decision making process. Basically what I want is to check the 3 columns for 3 different variables. if they match, then retun the number (in another column) of that row that matches all the variables Now i am looking for a function which can check the entries of the 3 columns and return the right corresponding number to that entry. For example, the three columns i have a Date, Customer, Defect Code First, it should read the date (month) , then the customer and then the defect code and then return the number of defects (which is in another column in book 3) I have inserted my version of code but obviously it doesn't recognize anything properly and work. =SUM(IF('[Book3.xls]Customer Returns (External)'!$A$3:$A$1200=$C$2, IF('[Book3.xls]Customer Returns (External)'!$C$3:$C$1200=C$3, IF('[Book3.xls]Customer Returns (External)'!$D$3:$D$1200=$A6, '[Book3.xls]Customer Returns (External)'!$J$3:$J$1200)))) A3:A1200 - List of all dates from Oct 2006 to Sept 2007 in Book 3 (column) C2 - Month cell from another workbook C3: C1200 - List of all customers in Book 3 (Column) C3 - Company cell from another workbook D3: D1200 - Defect Code column in Book 3 (Column) A6 - Defect Code Cell from another workbook J3: J1200 - Confirmed Defects (value that should be returned after satisfying these 3 conditions in book 3). I would appreciate if anyone can propose a better solution to this problem. Thanks in advance Swamy- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup 3 columns and return a result from another column
There are a lot of blanks after the data has been entered in book 3.
What I am doing is giving the user to enter more data so it automatically gets sorted in report 1. Say in the month of February 2007, the user is gonna enter more data, so I have alloted more blank rows for that. It is up to 1200 rows which most of them are filled and lot of them after that is unfilled. On Feb 21, 2:13 pm, Toppers wrote: I re-tested it and it works OK for me. As you are using text fields (for dates?) , check there are no leading/trailing blanks in ANY of the data. And did you enter with Ctrl+Shift+Enter (you should get {} brackets around the formula). If you cannot get it working, send w/book to me at (remove NOSPAM) " wrote: Thank you for your attempt. It returns me "#NA" and I dont know why this happens. I forgot to mention thatColumnsA, C and D are in text format and Column J (Number of defects) is in number format. Thanks for your help Swamy On Feb 21, 5:32 am, Toppers wrote: Try: =INDEX('[Book3.xls]Customer Returns (External)'!$J$3:$J$1200,MATCH(1,($C$2='[Book3.xls]Customer Returns (External)'!$A$3:$A$1200)*($C$3='[Book3.xls]Customer Returns (External)'!$C$3:$C$1200)*($A$6='[Book3.xls]Customer Returns (External)'!$D$3:$D$1200),0)) Entered with Ctrl+Shift+Enter HTH " wrote: Hi, I have a workbook (book 3) which consists of 8columnsof data. There are 3columnsof data which are very important. There is another workbook linked to book 3 which reads this entire data structure and performs decision making process. Basically what I want is to check the 3columnsfor 3 different variables. if they match, then retun the number (in another column) of that row that matches all the variables Now i am looking for a function which can check the entries of the 3 columnsand return the right corresponding number to that entry. For example, the threecolumnsi have a Date, Customer, Defect Code First, it should read the date (month) , then the customer and then the defect code and then return the number of defects (which is in another column in book 3) I have inserted my version of code but obviously it doesn't recognize anything properly and work. =SUM(IF('[Book3.xls]Customer Returns (External)'!$A$3:$A$1200=$C$2, IF('[Book3.xls]Customer Returns (External)'!$C$3:$C$1200=C$3, IF('[Book3.xls]Customer Returns (External)'!$D$3:$D$1200=$A6, '[Book3.xls]Customer Returns (External)'!$J$3:$J$1200)))) A3:A1200 - List of all dates from Oct 2006 to Sept 2007 in Book 3 (column) C2 - Month cell from another workbook C3: C1200 - List of all customers in Book 3 (Column) C3 - Company cell from another workbook D3: D1200 - Defect Code column in Book 3 (Column) A6 - Defect Code Cell from another workbook J3: J1200 - Confirmed Defects (value that should be returned after satisfying these 3 conditions in book 3). I would appreciate if anyone can propose a better solution to this problem. Thanks in advance Swamy- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| BIFF - Help!! lookup numbers in multiple columns and return one nu | Excel Worksheet Functions | |||
| LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
| lookup value and return result in column to left | Excel Worksheet Functions | |||
| I need a Lookup to return more than 1 result | Excel Worksheet Functions | |||
| return the column reference number of a function result | Excel Discussion (Misc queries) | |||
Linear Mode
