I'm trying to use an Array formula to return all values from a field for a given customer's name and then use something (I thought probably either MAX or MIN) to sort through them to get the correct one.
The formula is currently {=MAX(IF(A3='Sheet 2'!AH:AH,'Sheet 2'!G:G,""))} where A is my customer name, AH is the customer name on the other sheet and G is the field I want returned.
When I evaluate the formula for a customer I know only occurs once, as expected it returns a list of values most of which are blank and one of which is populated. When I then evaluate the MAX function it returns a 0. My assumption would be that the max between a populated value and a blank would be the populated value but clearly not.
Can anyone tell me please what function to use to pull my populated value out of my list of mostly blanks?
The formula is currently {=MAX(IF(A3='Sheet 2'!AH:AH,'Sheet 2'!G:G,""))} where A is my customer name, AH is the customer name on the other sheet and G is the field I want returned.
When I evaluate the formula for a customer I know only occurs once, as expected it returns a list of values most of which are blank and one of which is populated. When I then evaluate the MAX function it returns a 0. My assumption would be that the max between a populated value and a blank would be the populated value but clearly not.
Can anyone tell me please what function to use to pull my populated value out of my list of mostly blanks?
