• 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 to display all Value in Table Arreay with one cell look up value

Hi,

Is there any one can help me to do vlookup to display all value in Table Array with once cell look up value?
That's would be perfect if you don't use helper column.

Thanks,
Chanthan
 

Attachments

  • Vlookup to display all letter in Table Array.xlsx
    8.4 KB · Views: 6
Hi Chanthan,
Insert the following Array Formula in cell C4.

=IFERROR(INDEX($A$1:B19,IFERROR(SMALL(IF($A$14:$A$19=$D$1,ROW($A$14:$A$19),"-"),ROW()-ROW($C$3)),"-"),2),"")

Array Formulas are entered using Control + Shift + Enter instead of Enter.

Vijaykumar Shetye,
Panaji, Goa, India
 
Hi Chanthan,
Insert the following Array Formula in cell C4.
=IFERROR(INDEX($A$1:B19,IFERROR(SMALL(IF($A$14:$A$19=$D$1,ROW($A$14:$A$19),"-"),ROW()-ROW($C$3)),"-"),2),"")

Array Formulas are entered using Control + Shift + Enter instead of Enter.

Vijaykumar Shetye,
Panaji, Goa, India
1] In the above formula one IFERROR is enough, and avoid to use ROW() as increment number.

2] In C4, array formula (CSE enter), copied down :

=IFERROR(INDEX(B$14:B$19,SMALL(IF(A$14:A$19=D$1,ROW(A$14:A$19)-ROW(A$13)),ROWS($1:1))),"")

Or,

3] If you have Excel 2010 or above, try this non-array formula instead :

=IFERROR(INDEX(B$14:B$19,AGGREGATE(15,6,ROW(A$14:A$19)-ROW(A$13)/(A$14:A$19=D$1),ROWS($1:1))),"")

Regards
Bosco
 
In the attached, Vijaykumar's solution and another - it's a simple pivot table.
I've linked D1 to G1 for comparison, so change G1.

edit post posting: I should have included bosco_yip's solution too, but he's only just posted!
 

Attachments

  • Chandoo39491Vlookup to display all letter in Table Array.xlsx
    12.9 KB · Views: 2
Back
Top