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

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

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 -

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 -

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 -