Lookup 3 columns and return a result from another column

[mahadevan.swamy]

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 are: 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

 

[Toppers]

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

 

[mahadevan.swamy]

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

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

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 are: 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 -

 

[Toppers]

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
(e-mail address removed) (remove NOSPAM)

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

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

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 are: 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 -

 

[mahadevan.swamy]

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.

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
(e-mail address removed) (remove NOSPAM)

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

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
:
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 are: 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 -