index and match on 2 criteria
 

I have a table that i want to extract data from one column based on 2
conditions in other columns. The table has 4 columns, the first and second
are the columns that contain the data that has to meet the conditions, and
the fourth has the data the I want to retrieve. See below.

COMRAT SALREP COMRATDES COMPER1
ALB SL1D ABC Co. 4
AP SLA1 DEF Co. 6
ALB SLWA GHI Co. 2
AJ SL1G JKL Co. 7
ALB SL1H MNO Co. 10

Both the value in column A and the value in column B must match the
corresponding values from another worksheet and then return data from the
forth column. Example of a condition might be Column A=ALB and Column B=SLWA
then return 2.
I'm thinking that an index and match might be the way to go, but i need a
little help getting there.

index and match on 2 criteria
 

If it is a unique list use

=SUMPRODUCT(--(A2:A6="ALB"),--(B2:B6="SLWA"),--(D2:D6))

If this post helps click Yes
---------------
Jacob Skaria


"Picman" wrote:

I have a table that i want to extract data from one column based on 2
conditions in other columns. The table has 4 columns, the first and second
are the columns that contain the data that has to meet the conditions, and
the fourth has the data the I want to retrieve. See below.

COMRAT SALREP COMRATDES COMPER1
ALB SL1D ABC Co. 4
AP SLA1 DEF Co. 6
ALB SLWA GHI Co. 2
AJ SL1G JKL Co. 7
ALB SL1H MNO Co. 10

Both the value in column A and the value in column B must match the
corresponding values from another worksheet and then return data from the
forth column. Example of a condition might be Column A=ALB and Column B=SLWA
then return 2.
I'm thinking that an index and match might be the way to go, but i need a
little help getting there.

index and match on 2 criteria
 

Try this array formula

=INDEX(D2:D6,MATCH(1,(A2:A6=G1)*(B2:B6=H1),0))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Picman" wrote:

I have a table that i want to extract data from one column based on 2
conditions in other columns. The table has 4 columns, the first and second
are the columns that contain the data that has to meet the conditions, and
the fourth has the data the I want to retrieve. See below.

COMRAT SALREP COMRATDES COMPER1
ALB SL1D ABC Co. 4
AP SLA1 DEF Co. 6
ALB SLWA GHI Co. 2
AJ SL1G JKL Co. 7
ALB SL1H MNO Co. 10

Both the value in column A and the value in column B must match the
corresponding values from another worksheet and then return data from the
forth column. Example of a condition might be Column A=ALB and Column B=SLWA
then return 2.
I'm thinking that an index and match might be the way to go, but i need a
little help getting there.

index and match on 2 criteria
 

I guess I wasn't clear about the setup. This was only a sample of data from a
larger table that is only the source of the data and not the destination. The
destination is a cell on another worksheet that has adjacent cells with the
selection criteria (values that I want to find) in them.

Where A1 and B1 on the destination sheet = A1 and B1 on the table, return
the value from D1 to this cell


"Jacob Skaria" wrote:

If it is a unique list use

=SUMPRODUCT(--(A2:A6="ALB"),--(B2:B6="SLWA"),--(D2:D6))

If this post helps click Yes
---------------
Jacob Skaria


"Picman" wrote:

I have a table that i want to extract data from one column based on 2
conditions in other columns. The table has 4 columns, the first and second
are the columns that contain the data that has to meet the conditions, and
the fourth has the data the I want to retrieve. See below.

COMRAT SALREP COMRATDES COMPER1
ALB SL1D ABC Co. 4
AP SLA1 DEF Co. 6
ALB SLWA GHI Co. 2
AJ SL1G JKL Co. 7
ALB SL1H MNO Co. 10

Both the value in column A and the value in column B must match the
corresponding values from another worksheet and then return data from the
forth column. Example of a condition might be Column A=ALB and Column B=SLWA
then return 2.
I'm thinking that an index and match might be the way to go, but i need a
little help getting there.

index and match on 2 criteria
 

Didn't work see next posting

"Mike H" wrote:

Try this array formula

=INDEX(D2:D6,MATCH(1,(A2:A6=G1)*(B2:B6=H1),0))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Picman" wrote:

I have a table that i want to extract data from one column based on 2
conditions in other columns. The table has 4 columns, the first and second
are the columns that contain the data that has to meet the conditions, and
the fourth has the data the I want to retrieve. See below.

COMRAT SALREP COMRATDES COMPER1
ALB SL1D ABC Co. 4
AP SLA1 DEF Co. 6
ALB SLWA GHI Co. 2
AJ SL1G JKL Co. 7
ALB SL1H MNO Co. 10

Both the value in column A and the value in column B must match the
corresponding values from another worksheet and then return data from the
forth column. Example of a condition might be Column A=ALB and Column B=SLWA
then return 2.
I'm thinking that an index and match might be the way to go, but i need a
little help getting there.

index and match on 2 criteria
 

Didn't work is about as unhelpul as it gets in solving your problem and
there's no need to start a 'next posting'. Please explain in detail what
result you got and what you expected to get

Mike

"Picman" wrote:

Didn't work see next posting

"Mike H" wrote:

Try this array formula

=INDEX(D2:D6,MATCH(1,(A2:A6=G1)*(B2:B6=H1),0))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Picman" wrote:

I have a table that i want to extract data from one column based on 2
conditions in other columns. The table has 4 columns, the first and second
are the columns that contain the data that has to meet the conditions, and
the fourth has the data the I want to retrieve. See below.

COMRAT SALREP COMRATDES COMPER1
ALB SL1D ABC Co. 4
AP SLA1 DEF Co. 6
ALB SLWA GHI Co. 2
AJ SL1G JKL Co. 7
ALB SL1H MNO Co. 10

Both the value in column A and the value in column B must match the
corresponding values from another worksheet and then return data from the
forth column. Example of a condition might be Column A=ALB and Column B=SLWA
then return 2.
I'm thinking that an index and match might be the way to go, but i need a
little help getting there.

index and match on 2 criteria
 

Sorry, I had just responded to the previous suggestion when I saw yours come
in. I attempted your suggestion and didn't get the result that I was looking
for, and you are right "Didn't work" is not a helpful responce. At this point
I didn't think that repeating my attempt at a clarification was going to
help. Once again i'm sorry, I guess it's all about timing.

"Mike H" wrote:

Didn't work is about as unhelpul as it gets in solving your problem and
there's no need to start a 'next posting'. Please explain in detail what
result you got and what you expected to get

Mike

"Picman" wrote:

Didn't work see next posting

"Mike H" wrote:

Try this array formula

=INDEX(D2:D6,MATCH(1,(A2:A6=G1)*(B2:B6=H1),0))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Picman" wrote:

I have a table that i want to extract data from one column based on 2
conditions in other columns. The table has 4 columns, the first and second
are the columns that contain the data that has to meet the conditions, and
the fourth has the data the I want to retrieve. See below.

COMRAT SALREP COMRATDES COMPER1
ALB SL1D ABC Co. 4
AP SLA1 DEF Co. 6
ALB SLWA GHI Co. 2
AJ SL1G JKL Co. 7
ALB SL1H MNO Co. 10

Both the value in column A and the value in column B must match the
corresponding values from another worksheet and then return data from the
forth column. Example of a condition might be Column A=ALB and Column B=SLWA
then return 2.
I'm thinking that an index and match might be the way to go, but i need a
little help getting there.

index and match on 2 criteria
 

i tried playing around with you suggestion and it did work afterall.

Thank You Very Much!!

"Jacob Skaria" wrote:

If it is a unique list use

=SUMPRODUCT(--(A2:A6="ALB"),--(B2:B6="SLWA"),--(D2:D6))

If this post helps click Yes
---------------
Jacob Skaria


"Picman" wrote:

I have a table that i want to extract data from one column based on 2
conditions in other columns. The table has 4 columns, the first and second
are the columns that contain the data that has to meet the conditions, and
the fourth has the data the I want to retrieve. See below.

COMRAT SALREP COMRATDES COMPER1
ALB SL1D ABC Co. 4
AP SLA1 DEF Co. 6
ALB SLWA GHI Co. 2
AJ SL1G JKL Co. 7
ALB SL1H MNO Co. 10

Both the value in column A and the value in column B must match the
corresponding values from another worksheet and then return data from the
forth column. Example of a condition might be Column A=ALB and Column B=SLWA
then return 2.
I'm thinking that an index and match might be the way to go, but i need a
little help getting there.