Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 59
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 59
Default 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 -



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default Lookup 3 columns and return a result from another column

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 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 59
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
BIFF - Help!! lookup numbers in multiple columns and return one nu JB Excel Worksheet Functions 6 October 23rd 06 03:56 PM
LOOKUP and return the column heading for IF/THEN return for False NN Excel Discussion (Misc queries) 1 October 6th 06 12:24 PM
lookup value and return result in column to left Mark M Excel Worksheet Functions 3 April 23rd 06 08:47 PM
I need a Lookup to return more than 1 result joe1182 Excel Worksheet Functions 8 February 1st 06 03:52 PM
return the column reference number of a function result Mahendhra Excel Discussion (Misc queries) 2 May 16th 05 01:46 PM


All times are GMT +1. The time now is 04:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"