Modify array formula returning value associated with minimum price calculation

I have an array formula, which I do not understand enough to modify in column J of worksheet “Item_data”.

=IF(VLOOKUP(ItemTable[[#This Row],[PPT]],PriceTypeTable,3,FALSE)>0,LOOKUP(1,1/FREQUENCY(-9^9, IF((Basis="C")*(PriceType=ItemTable[[#This Row],[PPT]]),Multiplier,9^9)), CustPrcType),"")

The formula returns the first value of the Customer Type associated with the minimum customer price from Column G in worksheet “Type4_data”.

The requirement is now to return the first value of the Customer Type associated with the maximum customer price from Column G in worksheet “Type4_data”.

Could someone provide me with the correct syntax and explain?

Hi.

Actually there's no need for an array formula if you structure the syntax appropriately.

And if the only change is to return the first value associated with the maximum as opposed to the minimum, then, in J2:

=IF(VLOOKUP([@PPT],PriceTypeTable,3,FALSE)>0,LOOKUP(1,1/FREQUENCY(0, 1/((Basis="C")*(PriceType=[@PPT])*Multiplier)),CustPrcType),"")

Regards

You're very welcome!

Not only is there not a single error in column J of that sheet, there is not a single error in the entire sheet!

Regards

ErrorPic.jpg
This is what I'm showing, in column J. (attaching snapshot as jpeg-I think)
Sorry, guess I don't know how to do that, either.

I think you need to re-check your attachments.

I've just re-downloaded the 81.4KB version, navigated to the tab Item_data, and, like I said, there is not a single error in the whole of that sheet.

Regards

When I open the file directly. column J, worksheet "Item_data" is filled with "N/A". Can it be a setting in Excel itself that is causing the error when I open it, but not when you do? (I also downloaded the file using "save as" with the same resulting "N/A" error in all of column J (less the header row), worksheet "Item_data".

I'm baffled.

I've removed the original file, leaving only the file which when I open has the N/A error in column J on worksheet "Item_data".

Additionally I believe it is being caused when the 1/((Basis="C") part of the formula generates #DIV/0!

=IF(VLOOKUP(ItemTable[[#This Row],[PPT]],PriceTypeTable,3,FALSE)>0,LOOKUP(1,1/FREQUENCY(0, 1/(({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*(PriceType=ItemTable[[#This Row],[PPT]])*Multiplier)),CustPrcType),"")