hlookup all values by columns?

[Twatwood]

Similar to the vlookup solution that does rows, I am interested to lookup rows and display as columns. (all matches)

INPUT
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]StaffType[/TD]
[TD]StaffMember[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]Worker[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Worker[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[TD]****[/TD]
[/TR]
[TR]
[TD]Worker[/TD]
[TD]Harry[/TD]
[/TR]
[TR]
[TD]Worker[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Worker[/TD]
[TD]Joe[/TD]
[/TR]
</tbody>[/TABLE]



DESIRED OUTPUT

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Member1[/TD]
[TD]Member2[/TD]
[TD]Member3[/TD]
[TD]Member4[/TD]
[TD]Member5[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[TD]****[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Worker[/TD]
[TD]Bob[/TD]
[TD]Harry[/TD]
[TD]Joe[/TD]
[TD]John[/TD]
[TD]Tom[/TD]
[/TR]
</tbody>[/TABLE]
Note the input is unsorted and the output is sorted


I played with the vlookup and rows/index match solutions for a bit, but couldn't work out how to transpose the output or sort it.

 

[Mike Szczesny]

Can you use something like this? I replicated your input (A1:B8) and desired output (A10:F12). I put the formula in A11: [TABLE="width: 66"]
<tbody>[TR]
[TD="width: 66"] =IFERROR(INDEX($A$2:$A$8,SMALL(IF(FREQUENCY(IF($A$2:$A$8<>"",MATCH($A$2:$A$8,$A$2:$A$8,0)),ROW($A$2:$A$8)-ROW($A$2)+1),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($A$11:A11)))," ") Use Cntrl+Shift+Enter copy down. You should get your answers Manager and Worker.

I put the other formula in B11: [TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"] =IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$A$11,ROW($B$2:$B$8)-ROW($B$2)+1),COLUMNS($B$11:B11)))," ") copy across. Then tweak your formula to look for Worker. I enclosed =iferror(......," ") to remove error messages.

[TABLE="width: 411"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Member1[/TD]
[TD]Member2[/TD]
[TD]Member3[/TD]
[TD]Member4[/TD]
[TD]Member5[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[TD]Jane[/TD]
[TD]***[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Worker[/TD]
[TD]John[/TD]
[TD]Tom[/TD]
[TD]Harry[/TD]
[TD]Bob[/TD]
[TD]Joe[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

You would need to change in the f

 

[Vishal2050]

Hi, can you please brief about following part of formula as I have no idea about Row and column function

ROW($A$2:$A$8)-ROW($A$2)+1

 

[Mike Szczesny]

First of all, I am not an Mr Excel expert, but I will try my best to explain what is going on. The =row( function shows that range with numbers. For example, the row formula =row(B2:B8) will show up as 2 in your cell. The 2 shows is the first cell (B2) in the range. In actuality the formula consists of (2:3,4;5;6;7;8). You can see this when you highlight this formula and then press F9. For this formula to work, you want to have these numbers show as 1;2;3 etc. When you subtract the =row(first cell in range),your answer will be 0 (0;1;2;etc.). So that is why then you add that 1 to your formula to get (1;2;3;4, etc.). 1 is the first item in range, 2 the second item, etc. The columns formula simply shows numbers. For example =rows(B1:1b1)will show 1, then =rows(b1:b2) will show 2 as you copy down. This means it will produce the first, then second result. The columns formula works the same way when you want to show your answers copying across. Hope this helps