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/ex...260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mickn74" wrote in message
...
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