Hi All,
I've run into a dilemma where I need to return the first non blank value in a range. I've looked at using an array formula like this:
{=INDEX(range,MATCH(FALSE,ISBLANK(range),0))}
However I am running into a problem because the cells in my range are formulas. Here is an example of the type of formula in these cells:
=IF(RIGHT($A6,9)="Sheep Dog","Sheep Dog","")
Basically, what seems to be happening is that the index(match) array formula is treating "" as a non blank value. Is there a way around this?
To give a better understand of what I'm trying to achieve, I have a list of animals. In column A I have animal data (e.g. Red Sheep Dog, Large ****atoo). I don't care for colour or size, just type of animal. I only have a list of 20-25 animals so I've created a calculation cell for each type and it returns either the type I want or "". This is the range from which I want to return the first non blank value. Perhaps there is an easier way to do this then what I am doing, but I have no knowledge of VBA unfortunately.
Any help would be appreciated.
Thanks,
Ben
I've run into a dilemma where I need to return the first non blank value in a range. I've looked at using an array formula like this:
{=INDEX(range,MATCH(FALSE,ISBLANK(range),0))}
However I am running into a problem because the cells in my range are formulas. Here is an example of the type of formula in these cells:
=IF(RIGHT($A6,9)="Sheep Dog","Sheep Dog","")
Basically, what seems to be happening is that the index(match) array formula is treating "" as a non blank value. Is there a way around this?
To give a better understand of what I'm trying to achieve, I have a list of animals. In column A I have animal data (e.g. Red Sheep Dog, Large ****atoo). I don't care for colour or size, just type of animal. I only have a list of 20-25 animals so I've created a calculation cell for each type and it returns either the type I want or "". This is the range from which I want to return the first non blank value. Perhaps there is an easier way to do this then what I am doing, but I have no knowledge of VBA unfortunately.
Any help would be appreciated.
Thanks,
Ben
Last edited: