Going batty here.
I have 2 table arrays on two separate sheets in the same workbook.
I am trying to add a column to my primary table to take the Pricing Point (PP Number) from the other table(table 12)
I have a date in my primary table and a start and end date in table 12
I have a Sold-To Party in both tables that I am trying to match
I have a Case EAN in both tables that I am trying to match.
I think the below formula should work but it doesn't. I just get No PP even though I know that I have entries that match and should be presented.
Edit: Dates are formatted as dates and EAN and Sold-to party are both whole numbers and formatted as such.
Any ideas on what is wrong? or a different way to do this?
Thanks,
Andy
I have 2 table arrays on two separate sheets in the same workbook.
I am trying to add a column to my primary table to take the Pricing Point (PP Number) from the other table(table 12)
I have a date in my primary table and a start and end date in table 12
I have a Sold-To Party in both tables that I am trying to match
I have a Case EAN in both tables that I am trying to match.
I think the below formula should work but it doesn't. I just get No PP even though I know that I have entries that match and should be presented.
Edit: Dates are formatted as dates and EAN and Sold-to party are both whole numbers and formatted as such.
Excel Formula:
=XLOOKUP(1,(IF(AND(VALUE([@[Pricing Date]])>=VALUE(Table12[Pricing Start]),VALUE([@[Pricing Date]])<=VALUE(Table12[Pricing End]),[@[Sold-To Party]]=Table12[Sold-To Party],[@[EAN for CS]]=Table12[Case EAN]),1,0)),Table12[PP Number],"No PP",0,1)
Any ideas on what is wrong? or a different way to do this?
Thanks,
Andy
Last edited: