Using XLOOKUP or INDEX and MATCH with IF statement

[Adamfm00]

Hi,

I have been through a number of tutorials about using XLOOKUP, INDEX and MATCH but I can't get the syntax right, was hoping someone could help? Thanks

What I trying to do is: If the "Material" column is "Metal" match the number in the "Sold ID No" with the "Part No" column and display the value in the "Price" column in Sold Price

Part No	Toy	Price	Material			Sold ID No	Sold Price
10001	Scateboard	$10	Plastic			10003	If Material = "Metal" what is the Price
10002	Bike	$100	Metal			10002
10003	Train	$120	Plastic			10001
10004	Car	$50	Metal
10005	Airplane	$75	Metal
10006	Rocket	$60	Plastic

 

[Fluff]

Hi & welcome to MrExcel.
What should happen for the IDs that are not metal?

 

[Adamfm00]

Hi & welcome to MrExcel.
What should happen for the IDs that are not metal?

Just a zero value

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Part No	Toy	Price	Material			Sold ID No	Sold Price
2	10001	Scateboard	10	Plastic			10003	0
3	10002	Bike	100	Metal			10002	100
4	10003	Train	120	Plastic			10001	0
5	10004	Car	50	Metal
6	10005	Airplane	75	Metal
7	10006	Rocket	60	Plastic
Main

Cell Formulas
Range	Formula
H2:H4	H2	=FILTER($C$2:$C$7,($A$2:$A$7=G2)*($D$2:$D$7="metal"),0)

 

[Adamfm00]

Thanks Fluff
Sorry, one thing I forgot is that Column G needs to be an array as I only want to know the value if it is one of the numbers in G.

 

[Fluff]

Sorry but I don't understand, that formula only looks for the values in col G

 

[Adamfm00]

Yes sorry, I was trying to recreate what I was trying to do but it should be like below as I need prices against the list on the left but only for the numbers in the array on the right.

Part No	Toy	Price	Material	Sold Price			Sold ID No
10001	Scateboard	$10	Plastic	If Material = "Metal" what is the Price			10003
10002	Bike	$100	Metal	?			10002
10003	Train	$120	Plastic	?			10001
10004	Car	$50	Metal	?
10005	Airplane	$75	Metal	?
10006	Rocket	$60	Plastic	?

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Part No	Toy	Price	Material			Sold ID No
2	10001	Scateboard	10	Plastic	0		10003
3	10002	Bike	100	Metal	100		10002
4	10003	Train	120	Plastic	0		10001
5	10004	Car	50	Metal	0
6	10005	Airplane	75	Metal	0
7	10006	Rocket	60	Plastic	0
Main

Cell Formulas
Range	Formula
E2:E7	E2	=IF(AND(D2="metal",ISNUMBER(XMATCH(A2,$G$2:$G$4))),C2,0)

 

[Adamfm00]

Hi Fluff, plugged it into my file but it doesn't like it, saying inconsistent formula. I followed it and chose a line that should spit out a value but it's just putting out zero for some reason?

=IF(AND(C7722="Planned Maintenance", ISNUMBER(XMATCH(A7722,$K$2:$K$84))),H7722,0)

 

[Fluff]

Check that you don't have any circular references.