modify look up array to return values across row rather than down column

[martindwilson]

i have the following basic look up array which i use in all sorts of situations (with mods for sheet /ranges/row etc) works a treat but works down a column
how can i modify it to work across rows?
offset? perhaps
i tried substituting column for row() and it worked fine for first instance but it wont work on subsequent rows using that method.

=IF(ROW()-1>COUNTIF(Sheet1!$A$1:$A$2000,$A$1),"",INDEX(Sheet!$B$1:$B$2000,SMALL(IF(Sheet1!$A$1:$A$2000=$A$1,ROW(Sheet1!$A$1:$A$2000),""),ROW()-1)))

thanx

[NBVC]

Did you try?

=IF(COLUMN()-1>COUNTIF(Sheet1!$A$1:$A$2000,$A$1),"",INDEX(Sheet1!$B$1:$B$2000,SMALL(IF(Sheet1!$A$1:$A$2000=$A$1,ROW(Sheet1!$A$1:$A$2000),""),COLUMN()-1)))

confirmed with CSE keys and copied across

[martindwilson]

yes i did ,allowing for the fact i was in column 6 and it worked fine for finding value in a1 and returned results in d1,e1,f1,ect
but when i put it in say row 3 and want it to find whats in a3 it doesnt work
ah well it does work i just never tried it in row 1,2,3
i had headers and couldnt make the adjustment for rows!!!
i just tried it again and can see why. when dragged down it changes even tho absolute references eg $B$1 to $B$2
well it was after midnight il just not drag down then lol
thanx