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

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"


On 20 Sty, 07:11, mickn74 wrote:
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,

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

"Jarek Kujawa" wrote:

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"


On 20 Sty, 07:11, mickn74 wrote:
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

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),IN DEX(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


On 20 Éáí, 08:11, mickn74 wrote:
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

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

HTH

On 20 Ιαν, 12:43, wrote:
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),IN DEX(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

On 20 ÉáÃ*, 08:11, mickn74 wrote:



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- ΑπόκÏ�υψη κειμÎ*νου σε παÏ�άθεση -

- Εμφάνιση κειμÎ*νου σε παÏ�άθεση -

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

" wrote:

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),IN DEX(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


On 20 Ιαν, 08:11, mickn74 wrote:
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

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

On 20 Éáí, 13:31, mickn74 wrote:
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 *



" wrote:
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),IN DEX(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

On 20 Éáí, 08:11, mickn74 wrote:
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- Áðüêñõøç êåéìÝíïõ óå ðáñÜèåóç -

- ÅìöÜíéóç êåéìÝíïõ óå ðáñÜèåóç -

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

" wrote:

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

On 20 Ιαν, 13:31, mickn74 wrote:
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



" wrote:
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),IN DEX(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

On 20 Ιαν, 08:11, mickn74 wrote:
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- ΑπόκÏ�υψη κειμÎ*νου σε παÏ�άθεση -

- Εμφάνιση κειμÎ*νου σε παÏ�άθεση -