*Google Spreadsheet Post #2114*

*Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-27-2017*

*question by Brad O.*

**:**Array Formula for a variable range

I've been trying hard to get an array formula to work for my specific issue and I need some help with it.

I need an array formula that will auto expand into new rows. The formula needs to search all

__previous rows only__for a value from the new row. If it's present, insert a 1 into the cell, and if not, do nothing. Basically, I'm just checking if this is the first time the value appears in the sheet and if so, mark it with a 1. Future occurrences of the same value would be ignored.
Here's an example of the formula that works, if I manually copy it into each row:

=IF(countif($D$2:D9,D9)=1,1,"")

I've tried every way I could think of to convert this to a working Array formula that would auto expand into all future rows but I cannot seem to find the right way to do it.

Any suggestions on how to make this happen, either with Array Formula or other ideas would be greatly appreciated!