Findind the correct Line and later finding all non blank value.

[waiT_]

Hi, I am looking for something that can replace A3 and A4~A5 formulas, and be used in a table if way more columns compared to the example.

What it needs to do is Look for the LINE A1 is in the table, and then return the first non blank occurance header on a3, in a4 it will give the second non blank occurance in the line... and so on. b3,b4... Would return the Actual value, and for that I use =IFERROR(VLOOKUP(A1;TABLE;MATCH(A4;TABLE[#Headers];0));"")

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[/TR]
[TR]
[TD]Column2
[/TD]
[TD]XX[/TD]
[TD][/TD]
[TD]Name1[/TD]
[TD][/TD]
[TD]XX[/TD]
[TD][/TD]
[TD]YY[/TD]
[/TR]
[TR]
[TD]Column4[/TD]
[TD]YY[/TD]
[TD][/TD]
[TD]Name2[/TD]
[TD][/TD]
[TD][/TD]
[TD]ZZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula on A3
=IF(VLOOKUP(A1;TABLE;1;0)>0;TABLE[[#Headers];[Column1]];IF(VLOOKUP(A1;TABLE;2;0)>0;TABLE[[#Headers];[Column2]];IF(VLOOKUP(A1;TABLE;3;0)>0;TABLE[[#Headers];[Column3]];IF(VLOOKUP(A1;TABLE;4;0)>0;TABLE[[#Headers];[Column4]];""))))

Formula on A4
=IFERROR(IF((A1;TABLE;(MATCH(A3;TABLE[#Headers];0)+1))>0;VLOOKUP(A3;TABLE[#Headers];(MATCH(A3;TABLE[#Headers];0)+1);1);,IF(VLOOKUP(A3;TABLE[#Headers];(MATCH(A3;TABLE[#Headers];0)+2))>0;VLOOKUP(A3;TABLE[#Headers];(MATCH(A3;TABLE[#Headers];1)+2);1);IF(VLOOKUP(A3;TABLE;(MATCH(A3;TABLE[#Headers];1)+3);1)>0;VLOOKUP(A3;TABLE[#Headers];(MATCH(A3;TABLE[#Headers];1)+3);1);"")));"")

Formula on A5
=IFERROR(IF((A1;TABLE;(MATCH(A3;TABLE[#Headers];0)+1))>0;VLOOKUP(A4;TABLE[#Headers];(MATCH(A4;TABLE[#Headers];0)+1);1);,IF(VLOOKUP(A4;TABLE[#Headers];(MATCH(A4;TABLE[#Headers];0)+2))>0;VLOOKUP(A4;TABLE[#Headers];(MATCH(A4;TABLE[#Headers];1)+2);1);IF(VLOOKUP(A4;TABLE;(MATCH(A4;TABLE[#Headers];1)+3);1)>0;VLOOKUP(A4;TABLE[#Headers];(MATCH(A4;TABLE[#Headers];1)+3);1);"")));"")

Sorry for the format, Its my first post and my english is rusty.

 

[Aladin Akyurek]

I've tried to make it work more, and it seems that I was doing some mistakes.
Didnt even need to change your set up.

Thanks Aladin.

No problem. Glad to see that you sorted it out.