Returning Multiple values

[mickn74]

I have posted this request in the past yet the answers didn't help thanks for
those who tried.

I am putting in a postcode in Cell C6 = 4000

I want the formula to look up and find all the postcodes that represent 4000
in a table called TNI. Postcodes are located in column (A) in the TNI table
their may be multiple of the same post code. Each time it finds the postcode
it return the value or text in TNI Column (C) eg code QCBD/QBMH, QBMH.

TNI look up table has the same post code yet differeent valuse next to it eg
A B C
4000 Brisbane QCBD/QBMH
4000 Spring Hill QBMH
4000 All suburbs QBMN
4005 All suburbs QBMH

How do I get it to return QCBD/QBMH, QBMH, QBMN on the one line

IN a seperate cell I will also need to return the different suburbs in the
same cell for same post code is this the same

Can you pleae help

Regards

Mick

 

[Khoshravan]

As you mentione, 4000 is not unique. Which row do you want the excel to
return for you? You have to set a unique for looking up your table, like
looking for city or county name in addition to post code name.

 

[muddan madhu]

Assumed data is starts from row 1

you are putting 4000 in cell C6, In D6 put this formula and drag it
down

=IF(ISERROR(INDEX($C$1:$C$4,SMALL(IF($A$1:$A$4=$C$6,ROW($A$1:$A$4)),ROW
(1:1)),0)),"",INDEX($C$1:$C$4,SMALL(IF($A$1:$A$4=$C$6,ROW($A$1:$A
$4)),ROW(1:1)),0))

in Cell E6 put this formula and drag it down
=IF(D6<>"",E5&","&D6,"")

 

[mickn74]

Muddan,

Thanks for the help yet it wont return any values as I am assuming it is not
looking at the table i need ot get the information from.

The data table i am trying to look up and return values from is on another
sheet in this spreadsheet and the table is called TNI

I am entering the Postcode in $C$6 (this is a variable and always changes)
I am needing the formula to go into cell $F$17 on the same sheet.

This TNI table goes from A2 : E1053
Column A has the postcodes that I want to look up
Column C has the values and Text I want returned

TNI look up table has the same post code yet different values next to it eg
A B C
2 4000 Brisbane QCBD/QBMH
3 4000 Spring Hill QBMH
4 4000 All suburbs QBMN
5 4005 All suburbs QBMH

How do I get it to look at each of the postcodes 4000 and then return the
value in column C such as QCBD/QBMH, QBMH, QBMN on the one line.

I appreciate your help its obvious it is not an easy problem.

Cheers

Mick