How to use TRANSPOSE function?

[excelos]

Hello

I have this formula that returns the right address that I want:

=CELL("address",INDEX($H$38:$H$136,MATCH(0,$H$38:$H$136,0)))

But when I use the above formula in the TRANSPOSE function, it does not work:

=TRANSPOSE($H$38:CELL("address",INDEX($H$38:$H$136,MATCH(0,$H$38:$H$136,0))))

Any hint please?

 

[StephenCrump]

Try: =TRANSPOSE($H$38:INDEX($H$38:$H$136,MATCH(0,$H$38:$H$136,)))

 

[Aladin Akyurek]

Try rather: Control+shift+enter...

=TRANSPOSE($H$38:INDEX($H$38:$H$136,MATCH(0,$H$38:$H$136,0)))

 

[excelos]

Try rather: Control+shift+enter...

=TRANSPOSE($H$38:INDEX($H$38:$H$136,MATCH(0,$H$38:$H$136,0)))

Thanks, I tried that but it does not work, at least as I was expecting to.

It populates the formula cell with the first value of the source range. But when I drag it across the row, it does not transpose the range, but repeats the first value
How should it work really? Does it automatically populate the row with the source column? Or it needs me to drag it? And if the latter, why when I drag it, it repeats the value instead of copying the subsequent values from the source range?

Thanks

 

[BarryL]

you can't drag it, it needs to be part of an array. Select the cells that you want to populate (all of them! e.g. select A1:C1), then (with the cells still selected) enter the formula above with ctrl+shift+enter.