So given my 'random' example, the desired output would be this as below?

Result X,variance 1,score 1,variance 2,score 2
Result Y,variance 1,score 1
Result Z,variance 1,score 1,variance 2,score 2,variance 3,score 3,variance 4,score 4,variance 5,score 5,variance 5

I did this with =OFFSET() a long time ago. I'll see if I can find the old sheet. I do remember that it took a ton of "helper columns" that got hidden.

If that is indeed what you are trying to do, then one possible solution is in my attached sheet. It doesn't actually use VLOOKUP, but it does use
IF()
COUNTIF()
SMALL()
INDEX()
and
OFFSET()

Unfortunately Offset is a "volatile" function and updates as soon as you open the file. So it will prompt you to save, even if you haven't made any changes.

I left the error so that you can see there is something in the cells. Just add IFERROR() to those.

I put everything on one page so you can see the references easier.

Attached Files

ListFlattener.xlsx (18.4 KB, 13 views)