Array formula headache

Using Sheet2 of your posted workboo...

I created this helper column that identifies targeted rows just for demo purposes. (you can embed that formula in the main formulas)

Please Login or Register  to view this content.

These formulas begin the list of Fruit names and Quality

Please Login or Register  to view this content.

Copy those formulas down as far as you need.

Is that something you can work with?

You cannot enter Arrayed formulas into merged cells. You can merge the cells after the formula is there. So unmerge R:U

In R7, copied down

=IFERROR(INDEX(M$4:M$16, SMALL(IF(M$4:M$16<>"", ROW($M$4:$M$16)-ROW($M$4)+1), ROWS($A$1:$A1))),"")

In T7 copied down
=IFERROR(INDEX(N$4:N$16, SMALL(IF(N$4:N$16<>"", ROW($M$4:$M$16)-ROW($M$4)+1), ROWS($A$1:$A1))),"")
both arrayed formulas

The first thing you NEED to do is to get rid of all those merged cells. All they do is cause problems.

With that being done...

This array formula** entered in R7:

=IFERROR(INDEX(M:M,SMALL(IF(M$4:M$16<>"",ROW(M$4:M$16)),ROWS(R$7:R7))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This formula entered in T7:

=IF(R7="","",VLOOKUP(R7,M:N,2,0))

Select R7:T7 and copy down until you get blanks.

Reply discarded.