I have two sheets like below. In sheet1 I want find value by number to column
"value a" from Value column of Sheet2 if there is "a" in Type column. If
there isn't any "a":s I want return message "missing".
I tried use vlookup function but I don't how I select from two or four lines.

Sheet1
Number Name address Value a value b
101 ab line 1
102 ac line 2
103 cl line 3
104 xka line 4
105 xbx line 5
106 sd line 6
107 lsk line 7
108 jdöd line 8
109 ksldj line 9
110 x.n line 10
111 nsx. line 11
112 xösj line 12

Sheet2
Number Type Name Value
101 a ahsfhflhf 2
101 b ahsfhflhf 100
102 a ahsfhflhf 150
103 a ahsfhflhf 120
104 a ahsfhflhf 130
105 a ahsfhflhf 4
105 b ahsfhflhf 200
106 a ahsfhflhf 120
107 a ahsfhflhf 130
108 a ahsfhflhf 2
108 b ahsfhflhf 52
108 c ahsfhflhf 45
109 a ahsfhflhf 130
110 b ahsfhflhf 140
111 a ahsfhflhf 150


I s that clear enough that somebody could help me?

Esseja,

Just to clarify, for your scenario in the Value a column going down the
correct data should read:
2,Missing,150,Missing,120,130,4,Missing,120,130,2, Missing,Missing,130,Missing,150.

Let me know if that's correct
--
Brian


"Esseja" wrote:

I have two sheets like below. In sheet1 I want find value by number to column
"value a" from Value column of Sheet2 if there is "a" in Type column. If
there isn't any "a":s I want return message "missing".
I tried use vlookup function but I don't how I select from two or four lines.

Sheet1
Number Name address Value a value b
101 ab line 1
102 ac line 2
103 cl line 3
104 xka line 4
105 xbx line 5
106 sd line 6
107 lsk line 7
108 jdöd line 8
109 ksldj line 9
110 x.n line 10
111 nsx. line 11
112 xösj line 12

Sheet2
Number Type Name Value
101 a ahsfhflhf 2
101 b ahsfhflhf 100
102 a ahsfhflhf 150
103 a ahsfhflhf 120
104 a ahsfhflhf 130
105 a ahsfhflhf 4
105 b ahsfhflhf 200
106 a ahsfhflhf 120
107 a ahsfhflhf 130
108 a ahsfhflhf 2
108 b ahsfhflhf 52
108 c ahsfhflhf 45
109 a ahsfhflhf 130
110 b ahsfhflhf 140
111 a ahsfhflhf 150


I s that clear enough that somebody could help me?

I try to explain better.
There is only one row for number 101 in sheet1 but in the sheet2 there is
two rows for 101. In one row type is "a" and value is 2 in other row type is
"b" and value is 100. If I use next function in "sheet1 column Value a"
IF(ISNA(VLOOKUP(A2;Sheet2!$A$2:$D$16;4;FALSE)=TRUE );"Missing";VLOOKUP(A2;Sheet2!$A$2:$D$16;4;FALSE ))
It returns 2 but how I can select when number is 101 and type "b" that it
returns 100

maybe this helps you solve this promlem

"Brian" wrote:

Esseja,

Just to clarify, for your scenario in the Value a column going down the
correct data should read:
2,Missing,150,Missing,120,130,4,Missing,120,130,2, Missing,Missing,130,Missing,150.

Let me know if that's correct
--
Brian


"Esseja" wrote:

I have two sheets like below. In sheet1 I want find value by number to column
"value a" from Value column of Sheet2 if there is "a" in Type column. If
there isn't any "a":s I want return message "missing".
I tried use vlookup function but I don't how I select from two or four lines.

Sheet1
Number Name address Value a value b
101 ab line 1
102 ac line 2
103 cl line 3
104 xka line 4
105 xbx line 5
106 sd line 6
107 lsk line 7
108 jdöd line 8
109 ksldj line 9
110 x.n line 10
111 nsx. line 11
112 xösj line 12

Sheet2
Number Type Name Value
101 a ahsfhflhf 2
101 b ahsfhflhf 100
102 a ahsfhflhf 150
103 a ahsfhflhf 120
104 a ahsfhflhf 130
105 a ahsfhflhf 4
105 b ahsfhflhf 200
106 a ahsfhflhf 120
107 a ahsfhflhf 130
108 a ahsfhflhf 2
108 b ahsfhflhf 52
108 c ahsfhflhf 45
109 a ahsfhflhf 130
110 b ahsfhflhf 140
111 a ahsfhflhf 150


I s that clear enough that somebody could help me?

Esseja,

Here's how I did it. First I named all the data in the Number column on
Sheet 2 Number and the data in the Type column in Sheet 2 Type and named the
cell at the top of the Value Column (Cell D1) Start. Then I put in a column
to the left of Value a on Sheet 1 (Column E) and to the left of Value b on
Sheet 1 (Column G). In cell E2 put
=SUMPRODUCT((Number=$A1)*(Type="a"),Row(Number)) use Ctrl-Shft-Enter when
inputting this in that cell. Drag all the down to the bottom of the list.
Next in F2 which should be under Value a put
=IF(E2=0,"Missing",OFFSET(Start,E2-1,0)). Then drag down to the bottom of
the list. Repeat with Value b. Then you can hide the columns E and G.

Hope this helps
--
Brian


"Esseja" wrote:

I try to explain better.
There is only one row for number 101 in sheet1 but in the sheet2 there is
two rows for 101. In one row type is "a" and value is 2 in other row type is
"b" and value is 100. If I use next function in "sheet1 column Value a"
IF(ISNA(VLOOKUP(A2;Sheet2!$A$2:$D$16;4;FALSE)=TRUE );"Missing";VLOOKUP(A2;Sheet2!$A$2:$D$16;4;FALSE ))
It returns 2 but how I can select when number is 101 and type "b" that it
returns 100

maybe this helps you solve this promlem

"Brian" wrote:

Esseja,

Just to clarify, for your scenario in the Value a column going down the
correct data should read:
2,Missing,150,Missing,120,130,4,Missing,120,130,2, Missing,Missing,130,Missing,150.

Let me know if that's correct
--
Brian


"Esseja" wrote:

I have two sheets like below. In sheet1 I want find value by number to column
"value a" from Value column of Sheet2 if there is "a" in Type column. If
there isn't any "a":s I want return message "missing".
I tried use vlookup function but I don't how I select from two or four lines.

Sheet1
Number Name address Value a value b
101 ab line 1
102 ac line 2
103 cl line 3
104 xka line 4
105 xbx line 5
106 sd line 6
107 lsk line 7
108 jdöd line 8
109 ksldj line 9
110 x.n line 10
111 nsx. line 11
112 xösj line 12

Sheet2
Number Type Name Value
101 a ahsfhflhf 2
101 b ahsfhflhf 100
102 a ahsfhflhf 150
103 a ahsfhflhf 120
104 a ahsfhflhf 130
105 a ahsfhflhf 4
105 b ahsfhflhf 200
106 a ahsfhflhf 120
107 a ahsfhflhf 130
108 a ahsfhflhf 2
108 b ahsfhflhf 52
108 c ahsfhflhf 45
109 a ahsfhflhf 130
110 b ahsfhflhf 140
111 a ahsfhflhf 150


I s that clear enough that somebody could help me?

I didn't understand what you mean but I still try it
Thank You anyway

"Brian" wrote:

Esseja,

Here's how I did it. First I named all the data in the Number column on
Sheet 2 Number and the data in the Type column in Sheet 2 Type and named the
cell at the top of the Value Column (Cell D1) Start. Then I put in a column
to the left of Value a on Sheet 1 (Column E) and to the left of Value b on
Sheet 1 (Column G). In cell E2 put
=SUMPRODUCT((Number=$A1)*(Type="a"),Row(Number)) use Ctrl-Shft-Enter when
inputting this in that cell. Drag all the down to the bottom of the list.
Next in F2 which should be under Value a put
=IF(E2=0,"Missing",OFFSET(Start,E2-1,0)). Then drag down to the bottom of
the list. Repeat with Value b. Then you can hide the columns E and G.

Hope this helps
--
Brian


"Esseja" wrote:

I try to explain better.
There is only one row for number 101 in sheet1 but in the sheet2 there is
two rows for 101. In one row type is "a" and value is 2 in other row type is
"b" and value is 100. If I use next function in "sheet1 column Value a"
IF(ISNA(VLOOKUP(A2;Sheet2!$A$2:$D$16;4;FALSE)=TRUE );"Missing";VLOOKUP(A2;Sheet2!$A$2:$D$16;4;FALSE ))
It returns 2 but how I can select when number is 101 and type "b" that it
returns 100

maybe this helps you solve this promlem

"Brian" wrote:

Esseja,

Just to clarify, for your scenario in the Value a column going down the
correct data should read:
2,Missing,150,Missing,120,130,4,Missing,120,130,2, Missing,Missing,130,Missing,150.

Let me know if that's correct
--
Brian


"Esseja" wrote:

I have two sheets like below. In sheet1 I want find value by number to column
"value a" from Value column of Sheet2 if there is "a" in Type column. If
there isn't any "a":s I want return message "missing".
I tried use vlookup function but I don't how I select from two or four lines.

Sheet1
Number Name address Value a value b
101 ab line 1
102 ac line 2
103 cl line 3
104 xka line 4
105 xbx line 5
106 sd line 6
107 lsk line 7
108 jdöd line 8
109 ksldj line 9
110 x.n line 10
111 nsx. line 11
112 xösj line 12

Sheet2
Number Type Name Value
101 a ahsfhflhf 2
101 b ahsfhflhf 100
102 a ahsfhflhf 150
103 a ahsfhflhf 120
104 a ahsfhflhf 130
105 a ahsfhflhf 4
105 b ahsfhflhf 200
106 a ahsfhflhf 120
107 a ahsfhflhf 130
108 a ahsfhflhf 2
108 b ahsfhflhf 52
108 c ahsfhflhf 45
109 a ahsfhflhf 130
110 b ahsfhflhf 140
111 a ahsfhflhf 150


I s that clear enough that somebody could help me?