Returning Multiple values from A Vlookup

[mickn74]

I need a formula to return multiple values from a look up table as the number
appears multiple times in a column.
Reference cell is $C$6 eg = 4101

Current formula only return the first one it finds
=IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP($C$6,TNI,3,FALSE)))

I need the Cell to return all the values it finds from the one code eg =
QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW

Lookup table is TNI (A1:C1053) I need it to return all values in Column (C)
A B C
4077 All suburbs QRLE/QRLD
4078 Forest Lake QRLD/QLGH
4101 Highgate Hill QCBD
4101 South Brisbane QTNS/QCBD/QBMH/QMRE
4101 West End QCBD/QAGW
4102 Dutton Park QCBD
4102 Woolloongabba QTNS/QBMH/QMRE/QBBS
4103 All suburbs QTNS

Thanks
Michael

 

[Code Numpty]

Perhaps consider using Autofilter?

Sharon

 

[Jacob Skaria]

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=IF(COUNTIF(INDEX(TNI,,1),$C$6)<ROW(A1),"",
INDEX(TNI,SMALL(IF(INDEX(TNI,,1)=$C$6,
ROW(INDEX(TNI,,1))),ROW(A1)),3))

 

[Ashish Mathur]

Hi,

You may refer to my solution here -
http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com