poplulate cell based on two conditions
 

Need to fill a cell based on two conditions.
Compare a cell in worksheet A to column A in worksheet B, if found: compare
cell in worksheet B to row A, if found, populate orignal row and column (from
second compare) from cell xx (worksheet b).
IE: A2 (worksheet a) finds entry in column A, based on condition 1, C1
(worksheet b) finds entry in B1 (worksheet a) populate B2 (worksheet A) with
B2 (worksheet b).

ORIGNAL WORKSHEETS RESULTING
WORKSHEETS
worksheet a: worksheet b: workheet A:
A B C d A B C
A B C
1 iel01 iel02 iel03 igc019 5 iel01
iel01 iel02 iel03
2 igc019 igc019 2 iel03 igc019
5 2
3 uareit uareit 1 iel02 uareit
1
4 thatsit thatsit 1 iel01
thatsit 1 2 2
5 thatsit 2 iel02
6 thatsit 2 iel03


Thanks for any help you can provide.

poplulate cell based on two conditions
 

Re- post example worksheets.
ORIGNAL WORKSHEETS
worksheet a:
A B C d
1 iel01 iel02 iel03
2 igc019
3 uareit
4 thatsit
5
6
worksheet b:
A B C
1 igc019 5 iel01
2 igc019 2 iel03
3 uareit 1 iel02
4 thatsit 1 iel01
5 thatsit 2 iel02
6 thatsit 2 iel03

RESULTING WORKSHEET A:
worksheet a:
A B C d
1 iel01 iel02 iel03
2 igc019 5 2
3 uareit 1
4 thatsit 1 2 2
5
6

"Scire" wrote:

Need to fill a cell based on two conditions.
Compare a cell in worksheet A to column A in worksheet B, if found: compare
cell in worksheet B to row A, if found, populate orignal row and column (from
second compare) from cell xx (worksheet b).
IE: A2 (worksheet a) finds entry in column A, based on condition 1, C1
(worksheet b) finds entry in B1 (worksheet a) populate B2 (worksheet A) with
B2 (worksheet b).

ORIGNAL WORKSHEETS RESULTING
WORKSHEETS
worksheet a: worksheet b: workheet A:
A B C d A B C
A B C
1 iel01 iel02 iel03 igc019 5 iel01
iel01 iel02 iel03
2 igc019 igc019 2 iel03 igc019
5 2
3 uareit uareit 1 iel02 uareit
1
4 thatsit thatsit 1 iel01
thatsit 1 2 2
5 thatsit 2 iel02
6 thatsit 2 iel03


Thanks for any help you can provide.

poplulate cell based on two conditions
 

Put

=SUMPRODUCT((Sheet2!$A$1:$A$6=$A2)*(Sheet2!$C$1:$C $6=B$1)*(Sheet2!$B$1:$B$6))

in B2 and copy to the right and down as necessary. SUMPRODUCT works like
array formulas. For more info on array formulas, see
http://www.dailydoseofexcel.com/arch...array-formula/

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

Scire wrote:
Re- post example worksheets.
ORIGNAL WORKSHEETS
worksheet a:
A B C d
1 iel01 iel02 iel03
2 igc019
3 uareit
4 thatsit
5
6
worksheet b:
A B C
1 igc019 5 iel01
2 igc019 2 iel03
3 uareit 1 iel02
4 thatsit 1 iel01
5 thatsit 2 iel02
6 thatsit 2 iel03

RESULTING WORKSHEET A:
worksheet a:
A B C d
1 iel01 iel02 iel03
2 igc019 5 2
3 uareit 1
4 thatsit 1 2 2
5
6

"Scire" wrote:

Need to fill a cell based on two conditions.
Compare a cell in worksheet A to column A in worksheet B, if found:
compare cell in worksheet B to row A, if found, populate orignal row
and column (from second compare) from cell xx (worksheet b).
IE: A2 (worksheet a) finds entry in column A, based on condition 1,
C1 (worksheet b) finds entry in B1 (worksheet a) populate B2
(worksheet A) with B2 (worksheet b).

ORIGNAL WORKSHEETS
RESULTING WORKSHEETS
worksheet a: worksheet b:
workheet A: A B C d A B
C A B C
1 iel01 iel02 iel03 igc019 5 iel01
iel01 iel02 iel03
2 igc019 igc019 2 iel03
igc019 5 2
3 uareit uareit 1 iel02
uareit 1
4 thatsit thatsit 1 iel01
thatsit 1 2 2
5 thatsit 2 iel02
6 thatsit 2 iel03


Thanks for any help you can provide.

poplulate cell based on two conditions
 

Thanks. This works, small correction
=SUMPRODUCT((Sheet2!$A$1:$A$6=$A2)*(Sheet2!$C$1:$C $6=$B$1)*(Sheet2!$B$1:$B$6))

I worked on this for two days.

"Dick Kusleika" wrote:

Put

=SUMPRODUCT((Sheet2!$A$1:$A$6=$A2)*(Sheet2!$C$1:$C $6=B$1)*(Sheet2!$B$1:$B$6))

in B2 and copy to the right and down as necessary. SUMPRODUCT works like
array formulas. For more info on array formulas, see
http://www.dailydoseofexcel.com/arch...array-formula/

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

Scire wrote:
Re- post example worksheets.
ORIGNAL WORKSHEETS
worksheet a:
A B C d
1 iel01 iel02 iel03
2 igc019
3 uareit
4 thatsit
5
6
worksheet b:
A B C
1 igc019 5 iel01
2 igc019 2 iel03
3 uareit 1 iel02
4 thatsit 1 iel01
5 thatsit 2 iel02
6 thatsit 2 iel03

RESULTING WORKSHEET A:
worksheet a:
A B C d
1 iel01 iel02 iel03
2 igc019 5 2
3 uareit 1
4 thatsit 1 2 2
5
6

"Scire" wrote:

Need to fill a cell based on two conditions.
Compare a cell in worksheet A to column A in worksheet B, if found:
compare cell in worksheet B to row A, if found, populate orignal row
and column (from second compare) from cell xx (worksheet b).
IE: A2 (worksheet a) finds entry in column A, based on condition 1,
C1 (worksheet b) finds entry in B1 (worksheet a) populate B2
(worksheet A) with B2 (worksheet b).

ORIGNAL WORKSHEETS
RESULTING WORKSHEETS
worksheet a: worksheet b:
workheet A: A B C d A B
C A B C
1 iel01 iel02 iel03 igc019 5 iel01
iel01 iel02 iel03
2 igc019 igc019 2 iel03
igc019 5 2
3 uareit uareit 1 iel02
uareit 1
4 thatsit thatsit 1 iel01
thatsit 1 2 2
5 thatsit 2 iel02
6 thatsit 2 iel03


Thanks for any help you can provide.