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

 

[Adamfm00]

Hi Fluff, got caught up....sorry. I have remodeled it so it is closer to the actual file but it's not working still, don't know why?

Item Number	Item Name	Work Order Type	Priority	Month Year	Processing Date	Transaction Date	Value	Ordered QTY		Part No's	Total Cost
1001​	Item1	Test	Min	Sep-2022​	19/09/2022	19/09/2022	10.00​	1​		1001	0​
1002​	Item2	Test	Min	Apr-2023​	-		20.00​	2​		1002	0​
1002​	Item2	Planned	Min	Aug-2022​	25/08/2022	25/08/2022	20.00​	2​		1003	0​
1003​	Item3	Test	Min	Mar-2023​	07/04/2023	07/04/2023	30.00​	1​		1004	0​
1004​	Item4	Test	Min	Sep-2022​	24/09/2022	24/09/2022	40.00​	2​		1005	0​
1005​	Item5	Test	Min	Oct-2022​	19/10/2022	19/10/2022	50.00​	1​		1006	0​
1005​	Item5	Planned	Min	Oct-2022​	13/10/2022	13/10/2022	50.00​	2​			0​
1005​	Item5	Test	Min	Nov-2022​	14/11/2022	15/11/2022	50.00​	1​			0​
1006​	Item6	Test	Min	Feb-2023​	09/03/2023	09/03/2023	60.00​	1​			0​
1006​	Item6	Test	Min	Apr-2023​	07/04/2023	07/04/2023	60.00​	2​			0​

This was the function used in row 8 but still showing zero?

=IF(AND(D8="Planned",ISNUMBER(XMATCH(A8,$K$2:$K$7))),H8,0)

 

[Fluff]

Shouldn't that be C8 rather than D8?

 

[Adamfm00]

Shouldn't that be C8 rather than D8?

Spot on thanks Fluff, all working now

 

[Fluff]

You're welcome & thanks for the feedback.