Returning Multiple Values in a lookup

[mickn74]

I am putting in a postcode in Cell C6 = 4000

My formula to look up the postcode in Cell C6 is
=IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP($C$6,TNI,3,FALSE)))

I want the formula to look up all the postcodes that are 4000 in this Table
and return the 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
4004 All suburbs QBMH
4005 All suburbs QBMH

At the moment my current formula only returns QCBD/QBMH

Is tehre any way that i cna get it to return the whole lot QCBD/QBMH, QBMH
as there are 2 areas in the same postcode.

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

Thanks

Mick

 

[Jarek Kujawa]

without adjusting yr formula I'd think of inserting "QCBD/QBMH, QBMH"
in the 3rd column of TNI lookup table in the row where 1st column
equals "4000"

 

[mickn74]

Jarek,

Thanks for the thought, If I do the suggested way I need to change the whole
of the Look up table which is not possible as these tables are set standards
that change every 12 months, if I make changes like this It will get to
messy. It is loooking at Postcodes for an entire country.

I dont mind changing the formula if there is a better one that would return
the values.

Is there a formula that can do this

Regards,

michael

 

[vezerid]

First value using your own formula:

=IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP($C$6,TNI,3,FALSE)))

Say this is in cell K2 and you want subsequent values to show up
below, as far as needed. In K3:

=IF(ROWS($K$2:K3)<=COUNTIF(INDEX(TNI,0,1),$C$6),INDEX(TNI,MATCH(1,
(INDEX(TNI,0,1)=$C$6)*(COUNTIF($K$2:K2)=0),0),3),"")

Copy this formula down until you start getting blank cells.

HTH
Kostis Vezerides

 

[vezerid]

I forgot to say that the formula in K3 must be array-entered, using
key combination Shift+Ctrl+Enter.

HTH

 

[mickn74]

Kostis,

I copied the formula and referenced it to the F17 in where the value comes
up yet this does not allow me to accept teh formulas it says I have not
enough arguments.

The TNI look up table is on another sheet would that make any difference?

Any other suggestions

Regards,

Michael

 

[vezerid]

Of course, I made a couple of mistakes, having not tested the formula.
This formula is tested and assumes the list starts at F17, hence this
formula goes to F18:

=IF(ROWS($F$17:F18)<=COUNTIF(INDEX(TNI,0,1),$C$6),INDEX(TNI,MATCH(1,
(INDEX(TNI,0,1)=$C$6)*(COUNTIF($F$17:F17,INDEX(TNI,0,3))=0),0),3),"")

This formula will produce only unique values. I.e. QCBD/QBMH willonly
appear once.

Remember to commit with Shift+Ctrl+Enter

 

[mickn74]

Kostis,

It is retruning a #NA error I copied it as suggested

Am i doing something wrong?

Thanks for all your help I hope we can get it soon the waisted hours on this
a driving me nuts.

Thanks

mick