# Vlookup return 1st, 2nd, 3rd.... results Horizontal return

#### sivaprakasam

##### Member
Hi

I am using the Excel 2013

Vlookup return 1st, 2nd, 3rd.... results Horizontal return

Based Data Sheet is "Over All Data"

Formula Require Sheet is "Working Data" highlight in Yellow Color", Formula require based on the "Plant" from the sheet "Based Data"

e.g.. Excel File Enclosed

Kindle provide the formula for itself

#### Attachments

• 10.3 KB Views: 7
Last edited:

#### herofox

##### Active Member
hello ,you can use thia Array press (Ctrl+Shift+Enter)
Code:
``=IFERROR(INDEX('Over All Data'!\$C\$2:\$C\$50, SMALL(IF(\$A2='Over All Data'!\$A\$2:\$A\$50,IF(\$B2='Over All Data'!\$B\$2:\$B\$50, ROW(\$B\$2:\$B\$50)-1,"")), COLUMN()-2)),"")``

#### Attachments

• 11.3 KB Views: 2

#### GraH - Guido

##### Well-Known Member
Without CSE, maybe in
[C2] =IF(COLUMN(A\$1)>COUNTIF('Over All Data'!\$A:\$A,\$A2),"",INDEX('Over All Data'!\$C:\$C,AGGREGATE(15,6,ROW('Over All Data'!\$A:\$A)/('Over All Data'!\$A:\$A=\$A2),COLUMN(A\$1))))
drag across and down

#### Attachments

• 11.6 KB Views: 8

#### Fluff13

##### Active Member
If you have dynamic arrays you can use
=IF(A2="","",TRANSPOSE(FILTER('Over All Data'!\$C\$2:\$C\$100,'Over All Data'!\$A\$2:\$A\$100=A2,"")))

#### GraH - Guido

##### Well-Known Member
If you have dynamic arrays you can use
=IF(A2="","",TRANSPOSE(FILTER('Over All Data'!\$C\$2:\$C\$100,'Over All Data'!\$A\$2:\$A\$100=A2,"")))
Soon I hope. Craving for it...

#### Fluff13

##### Active Member
They certainly make some things easier & way faster.

#### Peter Bartholomew

##### Well-Known Member
@herofox's solution shows that the task is possible using traditional Excel programming techniques but it is @Fluff13's dynamic array solution that captures the intent. I believe that, with the introduction of dynamic arrays, the time has come to ditch direct naming conventions once and for all.
The location, 'Over All Data'!\$C\$2:\$C\$100', has no conceivable significance in terms of the problem being solved, and is a total dog when it comes to readability or providing succinct notation. The OP problem does push the boundaries of dynamic arrays, though, in that it highlights that spreadsheets do not offer a coherent way of handling 'arrays of arrays', in this case to give an array of filtered results.

The formula
= TRANSPOSE( FILTER( AllData[Vendor], AllData[Plant]=@Plant ) )
is pretty clear but it does need to be copied down. Copying down is usually a sad apology for something that should be a list or array operation but has failed to capture the structure of the solution. As yet there is no way round other than accepting the limitation. Dynamic arrays give so much, though, it is a bit unfair of me to carp at the limitations .

@GraH - Guido Not so long to wait. Soon you will be a real dude! I cracked early and put a corporate release of Office 365 on ice in order to spent more of my hard-earned pennies on a home version so that I could switch to insider fast!

#### GraH - Guido

##### Well-Known Member
They certainly make some things easier & way faster.
Candidate for understatement of the year ;-)

@Peter Bartholomew, my company follows the bi-annual release... So, indeed, I'm almost in... to play along with the big boys. It'll be a long month of May

#### sivaprakasam

##### Member
Hi.... Tnq to all for help...

i am trying another one formula

=IFERROR(INDEX('Over All Data'!\$C\$2:\$C\$50,SMALL(IF(\$A2='Over All Data'!\$A\$2:\$A\$50,ROW('Over All Data'!\$A\$2:\$A\$50)-ROW('Over All Data'!\$A\$2)+1),COLUMN('Over All Data'!A1))),"")