• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

  • Formula Require.xlsx
    10.3 KB · Views: 9
Last edited:
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

  • Formula Require1.xlsx
    11.3 KB · Views: 2
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

  • Copy of Formula Require.xlsx
    11.6 KB · Views: 8
@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 :cool: 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!
 
Hi.... Tnq to all for help...:DD:DD:DD

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))),"")
 
Back
Top