If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
index and match on 2 criteria
If it is a unique list use
=SUMPRODUCT(--(A2:A6="ALB"),--(B2:B6="SLWA"),--(D26)) 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. |
#3
|
|||
|
|||
index and match on 2 criteria
Try this array formula
=INDEX(D26,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. |
#4
|
|||
|
|||
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"),--(D26)) 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. |
#5
|
|||
|
|||
index and match on 2 criteria
Didn't work see next posting
"Mike H" wrote: Try this array formula =INDEX(D26,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. |
#6
|
|||
|
|||
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(D26,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. |
#7
|
|||
|
|||
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(D26,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. |
#8
|
|||
|
|||
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"),--(D26)) 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. |
Thread Tools | |
Display Modes | |
|
|