Using XLOOKUP or INDEX and MATCH with IF statement

Adamfm00

New Member
Joined
Nov 24, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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 NoToyPriceMaterialSold ID NoSold Price
10001Scateboard$10Plastic10003If Material = "Metal" what is the Price
10002Bike$100Metal10002
10003Train$120Plastic10001
10004Car$50Metal
10005Airplane$75Metal
10006Rocket$60Plastic
 
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 NumberItem NameWork Order TypePriorityMonth YearProcessing DateTransaction DateValueOrdered QTYPart No'sTotal Cost
1001​
Item1TestMin
Sep-2022​
19/09/202219/09/2022
10.00​
1​
1001
0​
1002​
Item2TestMin
Apr-2023​
-
20.00​
2​
1002
0​
1002​
Item2PlannedMin
Aug-2022​
25/08/202225/08/2022
20.00​
2​
1003
0​
1003​
Item3TestMin
Mar-2023​
07/04/202307/04/2023
30.00​
1​
1004
0​
1004​
Item4TestMin
Sep-2022​
24/09/202224/09/2022
40.00​
2​
1005
0​
1005​
Item5TestMin
Oct-2022​
19/10/202219/10/2022
50.00​
1​
1006
0​
1005​
Item5PlannedMin
Oct-2022​
13/10/202213/10/2022
50.00​
2​
0​
1005​
Item5TestMin
Nov-2022​
14/11/202215/11/2022
50.00​
1​
0​
1006​
Item6TestMin
Feb-2023​
09/03/202309/03/2023
60.00​
1​
0​
1006​
Item6TestMin
Apr-2023​
07/04/202307/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)
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Shouldn't that be C8 rather than D8?
 
Upvote 1
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top