search and match a record

[pals70423]

Hi All,
Need your help.i have requirement where i have following data


sheet 1
--------

data value
----- -----
test1 4
test2 0
test 3 0
test4 6
test7 1


sheet 2
--------


data1 data2 value

test1	test2	4
test3	test4	6
test6	test7	1

<tbody>
</tbody>

i have sheet1 where there are 2 columns data and value and sheet 2 which has 3 columns.i need to populate the sheet 1 values in the sheet 2 values based on the below criteria

search for the value of the column "data" in the sheet 2 and populate the non zero value in sheet 2.the columns are dynamic in sheet 1

Thanks
pal

 

[cyrilbrd]

what if both values are > to 0?

 

[pals70423]

what if both values are > to 0?

It will not be the case..only one value will be greater than 0.

Thanks

 

[cyrilbrd]

Not sure if elegant:

Excel 2010
	A	B	C	D	E	F	G
1	data	value			data1	data2	value
2	test1	4			test1	test2	4
3	test2	0			test3	test4	5
4	test3	5			test5	test6	6
5	test4	0
6	test5	0
7	test6	6
pals70423

formula in G2 is =SUM(IF($A$2:$A$7=E2,$B$2:$B$7),IF($A$2:$A$7=F2,$B$2:$B$7)) Ctrl + Shift + Enter not just enter on a PC or command + Return on a MAC.
or non array =INDEX($B$2:B7,MATCH($E2,$A$2:A7,0))+INDEX($B$2:B7,MATCH($F2,$A$2:A7,0)) copied down till needed.

 

[pals70423]

Not sure if elegant:
Excel 2010

	A	B	C	D	E	F	G
1	data	value			data1	data2	value
2	test1	4			test1	test2	4
3	test2	0			test3	test4	5
4	test3	5			test5	test6	6
5	test4	0
6	test5	0
7	test6	6

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

pals70423​

formula in G2 is =SUM(IF($A$2:$A$7=E2,$B$2:$B$7),IF($A$2:$A$7=F2,$B$2:$B$7)) Ctrl + Shift + Enter not just enter on a PC or command + Return on a MAC.
or non array =INDEX($B$2:B7,MATCH($E2,$A$2:A7,0))+INDEX($B$2:B7,MATCH($F2,$A$2:A7,0)) copied down till needed.

Hi
it is working perfectly..thanks a lot for your help.

Thanks
pal

 

[cyrilbrd]

Most welcome. Thanks for your feedback.