Hello,



As you will see in the attached file, I need to extract the details "variance" and "score" of "RESULT X" from Sheet 2 and place them in the correspondent fields in Sheet 1, horizontally.

So far, I've only managed to use VLOOKUP to extract "variance" and "score" from Sheet 2 - B5 and C5, but how about extracting B6 and C6(and so on) of the same "RESULT X" and put them in line like in Sheet 1?

Any help please?

Many thanks in advance!

Attached Files

1.xlsx (9.2 KB, 11 views)

How many variations of 'Result X' will there be? Will it always be the same? Like:
Result X1
Result X2
Result X3
Result Y1
Result Y2
Result Y3
Result Z1
Result Z2
Result Z3
?

Or is it random, like:
Result X1
Result X2
Result Y1
Result Z1
Result Z2
Result Z3
Result Z4
Result Z5
?

One type of "result" with different "scores" and "variances" each line.

Your "random" option.

Redefining my request:

How can I convert those values from being vertically(sheet2) to horizontally(sheet1)?

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, 11 views)