Advice on copying cells into second tab

I presume that it's this bit you need to understand:

=IFERROR(INDEX(Sheet1!$B$2:$B$55,MATCH(0,INDEX(COUNTIF($B$8:$B8,Sheet1!$B$2:$B$55),0),0)),"")

the INDEX in the middle of it is just an Excel "trick" to transform an array formula into a non-array (ordinary) formula, which many users prefer. The unadapted formula is:

=IFERROR(INDEX(Sheet1!$B$2:$B$55,MATCH(0,COUNTIF($B$8:$B8,Sheet1!$B$2:$B$55),0)),"")

Being an array formula, it would need to be set using CTRL-SHIFT-Enter.

At its heart is this bit:



COUNTIF($B$8:$B8,Sheet1!$B$2:$B$55)

which returns 1 or 0 depending on whether the value in $B$8:$B8 has previously been found in the range $B$2:$B$55. In B9, the value in B8 (a blank cell - but can be a header) will never previously been found in that range, so it returns a series of 0s. The bit in red returns the row corresponding to the first zero. In B9, it will be the first row. The INDEX bit in cyan returns the corresponding value from column B - in this case BD. When the formula is copied into B9, any cells inthe column containing BD will return a 1 in the countif, so the first row that returns a zero in the countif will be passed to the INDEX function, and KR will be returned.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.