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 

Perhaps consider using Autofilter?

Sharon

"mickn74" wrote:

> 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 
> 

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))

-- 
Jacob


"mickn74" wrote:

> 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 
> 

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

"mickn74" <mickn74@discussions.microsoft.com> wrote in message 
news:5824E1AE-1553-4B81-9640-C7B60B6B9C82@microsoft.com...
> 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
>