Here is another approach:
How It Works
=ArrayFormula(...) signifies that the formula will process an entire range and not just one cell.
Now, working from the inside out...
REPT(ROW(A:A)&"@",A:A) will add the
@ symbol onto the end of each row number and then repeat that string however many times is listed in A:A. For instance, if row 1 Column A indicates to repeat 1x, the string for that row will be
1@. If row 2 Column A indicates it is to repeat 2x, the sting for that row will be
All of those strings will be joined into one long string (e.g.,
That long string will be split apart at the
@ symbol into a long virtual row of individual numbers, e.g.:
1 2 2 3 3 3...
That long virtual row of individual numbers will be transposed into a column of the same numbers, e.g.:
Each of those individual numbers in the column will be looked up within a virtual array composed of all row numbers in column one and all B:B text in column two. When each exact match is found (signified by
FALSE), the Column
2 data will be returned.