Match function questions

[MableMa]

Dear,

Please help

Worksheet 1

1	A	B	C
2	123456		290
3	234567

<tbody>
</tbody>

Worksheet 2

1	<strike></strike>A	B<strike></strike>	C
2	123456	SP	290
3	234567

<tbody>
</tbody>

I would like to set a formula in Worksheet 1 cell C2.

I require to match Worksheet 1 Column A to Worksheet 2 Column A and if Worksheet 2 Column B has "SP". Worksheet 1 cell C1 should return the value of Worksheet 2 C2.

Please kindly help.

 

[sandy666]

you can try PowerQuery

source				result
Column1	Column2	Column3		Column1	Column2	Custom
123456​	SP	290​		123456​	SP	290​
234567​		444​		234567​
444555​		333​		444555​
666777​	SP	111​		666777​	SP	111​

[SIZE=1]// Table6
let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}}),
    IF = Table.AddColumn(Type, "Custom", each if Text.Contains([Column2], "SP") then [Column3] else null),
    Replace = Table.ReplaceErrorValues(IF, {{"Custom", null}}),
    RC = Table.RemoveColumns(Replace,{"Column3"})
in
    RC[/SIZE]

 

[MableMa]

you can try PowerQuery

source				result
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column3[/COLOR]		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Custom[/COLOR]
123456​	SP	290​		123456​	SP	290​
234567​		444​		234567​
444555​		333​		444555​
666777​	SP	111​		666777​	SP	111​

<tbody>
</tbody>

[SIZE=1]// Table6
let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}}),
    IF = Table.AddColumn(Type, "Custom", each if Text.Contains([Column2], "SP") then [Column3] else null),
    Replace = Table.ReplaceErrorValues(IF, {{"Custom", null}}),
    RC = Table.RemoveColumns(Replace,{"Column3"})
in
    RC[/SIZE]

I have not used Power Query before. Could you please kindly give me some steps for the above code?

Many thanks.

 

[alansidman]

Look at this link

[URL="https://excel.solutions/2017/11/power-query-paste-code-video/"][B]Power Query[/B]-How to use Mcode[/URL]

 

[AlanY]

try

Book1
	A	B	C
1	123456	SP	290
2	234567
Sheet2

Book1
	A	B	C
1	123456		290
2	234567
Sheet1

Cell Formulas
Range	Formula
C1	=IF(INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,0))="SP",INDEX(Sheet2!C:C,MATCH(A1,Sheet2!A:A,0)),"")

 

[Eric W]

If the values in Sheet2!A:A are unique, and the values in Sheet2!C:C are numeric, you can simply use in Sheet1!C2

=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,"SP")

A "not found" condition will show as a 0. If you just want a space, there are several ways to do that.

 

[MableMa]

try

	A	B	C
1	123456	SP	290
2	234567

<tbody>
</tbody>

Sheet2​

	A	B	C
1	123456		290
2	234567

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =IF(INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,0))="SP",INDEX(Sheet2!C:C,MATCH(A1,Sheet2!A:A,0)),"") <tbody> </tbody>

<tbody>
</tbody>

However I cannot get the second and third row right. Could you please give me more advises.

	A	B	C
1	123456	SP	290
2	234567
3	345678
4	456789	SP	600

<tbody style="border-collapse: collapse; width: auto;">
</tbody>

Sheet2​

​

	A	B	C
1	123456		290
2	234567
3	456789​ <strike></strike>	<strike></strike>	?
4	123456

<tbody style="border-collapse: collapse; width: auto;">
</tbody>

Sheet1​

Many thanks for your help.

 

[AlanY]

it should work

Book1
	A	B	C
1	123456		290
2	234567
3	456789		600
Sheet1

Cell Formulas
Range	Formula
C1	=IF(INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,0))="SP",INDEX(Sheet2!C:C,MATCH(A1,Sheet2!A:A,0)),"")

Book1
	A	B	C
1	123456	SP	290
2	234567
3	345678
4	456789	SP	600
Sheet2